Sep 15 2022 09:09 AM
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?
Sep 15 2022 12:34 PM
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.
Sep 16 2022 01:12 AM
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.