1

I have a table in which the headers should be all the unique start and end times that are in another table. However, when I use the UNIQUE function, the result still contains duplicates. Does anyone know why this might be the case?

This is the formula I'm using:

=TRANSPOSE(SORT(FILTER(UNIQUE(FLATTEN(B3:B,E3:E)),UNIQUE(FLATTEN(B3:B,E3:E))<>""),1,1))

spreadsheet with problem

As you can see, there are duplicate values for 04:00 and 05:00.

I've checked to make sure the values are all formatted the same way but I'm still getting this problem.

Here's the link to a spreadsheet with the problem.

Any help would be greatly appreciated.

1 Answer 1

0

The problem stems from using the FILTER function. It uses relative positions.

Use the QUERY function instead:

=TRANSPOSE(SORT(UNIQUE(QUERY(FLATTEN(B3:B,E3:E),"WHERE Col1 IS NOT NULL"))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.