Dec 25 2021 12:41 PM - edited Dec 25 2021 12:44 PM
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)
Dec 25 2021 10:32 PM
@Tony2021 How about = UNIQUE(A2:B9) as shown in the picture below?
Dec 26 2021 06:17 AM
Dec 26 2021 07:15 AM
Solution@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.
Dec 26 2021 09:45 AM
Dec 26 2021 10:01 AM
Dec 26 2021 10:13 AM
@Tony2021 The link below would be a good place to start.
https://exceloffthegrid.com/power-query-introduction/
The idea is to connect to the data, and do the necessary transformations, groupings etc. How exactly depends on the structure of the data. In your case I can imagine you select the columns you want to check. Merge them and then remove duplicates. But I might change my mind if I see the data. And, as always, there are probably many other ways to achieve something similar.
Dec 26 2021 10:14 AM
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)
)
Dec 26 2021 04:42 PM
Dec 26 2021 07:15 AM
Solution@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.