Unique formula to search multiple columns

New Contributor

I have an export of data of the same field, in multiple columns - sometimes it has been entered into multiple columns, sometimes into different columns between a and d. 

 

I want to extract all the unique values into a single list. 

 

=UNIQUE($A:$d)

 

doesn't work, so what can I do?  

2 Replies

@ruthshaw 

You'd need to collect all values into a single column, then apply UNIQUE to that column.

If you are a Microsoft 365 insider, you can use the new VSTACK function to collect the values into a single column.

Thanks @Hans Vogelaar  I didn't know about vstack and it doesn't appear as an option so maybe I'm on the wrong license.

 

For anyone interested in how to collate multiple columns to a single column I solved that with a named array of the columns: 

 

=SORT(INDEX(Course,1+INT((ROW(A1)-1)/COLUMNS(Course)),MOD(ROW(A1)-1+COLUMNS(Course),COLUMNS(Course))+1),TRUE,TRUE)

 

where 'course' is the four columns containing the info I want to keep a list of unique values of, and cell a1 can be almost any column heading or empty, but not the one containing the Unique formula.