I have a short list of about 80 companies that are all the results of Xlookups from a larger list of about 400. I am trying to get a sorted list of unique names. There are some blanks in the original list of 400 and my Xlookup is putting 0 in place of an error. Using the Unique function I am getting a list of unique names, but it is delivering the 0 twice as if there are two separate 0's. I'm not sure why this is breaking down. I've tested if they are equivalent and Excel tells me they are the same value so I do not know why it is outputting two 0's. At first I thought Excel was treating errors and blanks differently, but when I changed the output for errors it still duplicated the 0's.
Here is my formula and the output is below.
=SORT(UNIQUE(FILTER('Active Project Listing'!$L$3:$L$150,'Active Project Listing'!$E$3:$E$150=1)))
I guess you have both zeroes and blanks, these are different. Like here
Left result is by
=SORT( UNIQUE( FILTER(B2:B9, A2:A9) ) )If to filter blanks (right column)
=LET(f, SORT( UNIQUE( FILTER(B2:B9, A2:A9) ) ), FILTER(f, f<>"") )assuming your texts are not more than 255 characters long.