Unique formula to search multiple columns

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...

Formulas and Functions

HansVogelaar

MVP

Sep 15, 2022

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.

ruthshaw

Copper Contributor

Sep 16, 2022

Thanks HansVogelaar 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.

Forum Discussion

Unique formula to search multiple columns

Resources