Forum Discussion
Unique formula to search multiple columns
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 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.