Forum Discussion

ruthshaw's avatar
ruthshaw
Copper Contributor
Sep 15, 2022

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 unique values into a single list. 

 

=UNIQUE($A:$d)

 

doesn't work, so what can I do?  

Formulas and Functions

2 Replies

Sort By
  • HansVogelaar's avatar
    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's avatar
      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. 

       

       

       

       

Resources