Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Dec 25, 2021
Solved

Dynamic Unique List - 2 Columns

Hello,

 

I can create a dynamic unique list with 1 column of data but what I need now is a dynamic unique list of data in 2 columns.  I have an example formula in the attached file but it only works on 1 column.  

 

Please see attached file.  

grateful for the help. 

Example (that is in the file)

 

excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 26, 2021

    Tony2021 Never tried that before but perhaps like in the attached file.

     

    If this is not what you had in mind, I'd suggest you look into Power Query. Much more flexible and robust for these kind of tasks. At least in my humble opinion. 

8 Replies

    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Dec 26, 2021
      Hello Riny, thats ingenious. I have a follow up though. In my "real" data set there are many columns between the columns I need for the array. How can I use 2 non contigous columns? Imagine that there is a column between columns A and B making the array to reference only column A and C. I tried to modify the array (with AND) but it didnt seem to work. I got a #value error. I hope that makes sense. Let me know if you have any questions.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Dec 26, 2021

        Tony2021 

        I rather liked Riny_van_Eekelen 's idea of applying a FILTER horizontally to allow the columns to be selected dynamically.  If, on the other hand, the columns to be used are known ahead of time, the array to be filtered can be built using CHOOSE.  For example

        = UNIQUE(
    CHOOSE({1,2,3}, 
      Table1[Column1],
      Table1[Column3],
      Table1[Column5]
    )
  )

        Being a modern function UNIQUE works as well on arrays as it does ranges.  Using an Excel Table and ignoring the possibility of blanks. Riny's formula reduces to

        = UNIQUE(
    FILTER(Table1,criterion)
  )

         

Resources