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. 




doesn't work, so what can I do?  

2 Replies


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: 




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.