Unique combinations from two columns
-
- NewLounger
- Posts: 5
- Joined: 08 Mar 2023, 21:10
Unique combinations from two columns
I have a Table of sales information for multiple customers (Column A) and multiple products (Column B). Since this is a multi-year table, a lot of possible combinations are duplicated or more. Short of making a pivot table from these columns, is there a way to use the UNIQUE function, alone or in combination, to give me a list of every possible combination of the two with the results in two columns? Thanks.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unique combinations from two columns
A belated welcome to Eileen's Lounge!
Let's say the data in column A and B are in A2:B500.
In a cell elsewhere, enter the formula
=UNIQUE(A2:B500)
to return all unique combinations. If you want to sort the result by customer, then by product, use
=SORT(UNIQUE(A2:B500), {1, 2})
Let's say the data in column A and B are in A2:B500.
In a cell elsewhere, enter the formula
=UNIQUE(A2:B500)
to return all unique combinations. If you want to sort the result by customer, then by product, use
=SORT(UNIQUE(A2:B500), {1, 2})
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 08 Mar 2023, 21:10
Re: Unique combinations from two columns
Thanks. I guess the thing that was messing this up for me was my data were not in contiguous columns - such as one set of information in Column B, the other in Q. Attempting "unique({tblSales[PRODNO],tblSales[GROUP]})" failed. Maybe just trying to be too adventurous, but I can see a lot of possibilities for other applications.
And thanks for the welcome, though it's really a 'welcome back' - I was on Woody's wopr.com a very long time ago. I still have some forum printouts I saved for reference - 20 years old and I still keep them handy. It's nice to see the Lounge has survived for so long.
And thanks for the welcome, though it's really a 'welcome back' - I was on Woody's wopr.com a very long time ago. I still have some forum printouts I saved for reference - 20 years old and I still keep them handy. It's nice to see the Lounge has survived for so long.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unique combinations from two columns
Let's say that PRODNO and GROUP are the 2nd and 17th columns of tblSales, respectively. You could then use
=UNIQUE(INDEX(tblSales,SEQUENCE(ROWS(tblSales)),{2,17}))
or, for the sorted version
=SORT(UNIQUE(INDEX(tblSales,SEQUENCE(ROWS(tblSales)),{2,17})),{1,2})
(Woody's Lounge - that was a long time ago! You'll still find several users from that time here...)
=UNIQUE(INDEX(tblSales,SEQUENCE(ROWS(tblSales)),{2,17}))
or, for the sorted version
=SORT(UNIQUE(INDEX(tblSales,SEQUENCE(ROWS(tblSales)),{2,17})),{1,2})
(Woody's Lounge - that was a long time ago! You'll still find several users from that time here...)
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 08 Mar 2023, 21:10
Re: Unique combinations from two columns
Let's see if I can make this even more complicated! "{2,17}" would be good, but what if the table was large and I didn't know which position each table column was in, but did know the field names? I tried using a formula to calculate and fill in the values for the {} array part, but Excel did not like what I tried.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unique combinations from two columns
Unfortunately, we cannot use cell references or calculated expressions in a literal array {...}.
Here is a rather clunky solution:
=UNIQUE(INDEX(tblSales, SEQUENCE(ROWS(tblSales)), TRANSPOSE(LET(h, TRANSPOSE(((tblSales[#Headers]="PRODNO")+(tblSales[#Headers]="GROUP")))*SEQUENCE(COLUMNS(tblSales[#Headers])), FILTER(h, h<>0)))))
Sorted:
=SORT(UNIQUE(INDEX(tblSales, SEQUENCE(ROWS(tblSales)), TRANSPOSE(LET(h, TRANSPOSE(((tblSales[#Headers]="PRODNO")+(tblSales[#Headers]="GROUP")))*SEQUENCE(COLUMNS(tblSales[#Headers])), FILTER(h, h<>0))))), {1,2})
Here is a rather clunky solution:
=UNIQUE(INDEX(tblSales, SEQUENCE(ROWS(tblSales)), TRANSPOSE(LET(h, TRANSPOSE(((tblSales[#Headers]="PRODNO")+(tblSales[#Headers]="GROUP")))*SEQUENCE(COLUMNS(tblSales[#Headers])), FILTER(h, h<>0)))))
Sorted:
=SORT(UNIQUE(INDEX(tblSales, SEQUENCE(ROWS(tblSales)), TRANSPOSE(LET(h, TRANSPOSE(((tblSales[#Headers]="PRODNO")+(tblSales[#Headers]="GROUP")))*SEQUENCE(COLUMNS(tblSales[#Headers])), FILTER(h, h<>0))))), {1,2})
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 08 Mar 2023, 21:10
Re: Unique combinations from two columns
Ouch, instant headache! Serves me right for trying to be too lazy to count columns. There is a lot of value in writing something to be dynamic instead of static, to avoid having to manually change formulas, so hopefully it can be added as a native feature someday.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unique combinations from two columns
The formulas that I posted should do that...
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 08 Mar 2023, 21:10
Re: Unique combinations from two columns
Hans, I have got to thank you bigly for this!
I've tinkered around with it quite a bit over the last couple of months, to the point that I am using it in multiple spreadsheets, anywhere I need to build a quick list of unique values or combinations of values from a table.
A recent iteration gives me a lateral list for building an incredibly flexible report - unique values across, for lookups to them below:
I've saved this in my 'Memos' module in my PERSONAL.xlsb for quick reference, to copy and tweak wherever needed.
I've tinkered around with it quite a bit over the last couple of months, to the point that I am using it in multiple spreadsheets, anywhere I need to build a quick list of unique values or combinations of values from a table.
A recent iteration gives me a lateral list for building an incredibly flexible report - unique values across, for lookups to them below:
Code: Select all
=TRANSPOSE(SORT(UNIQUE(INDEX(FILTER(tblAlloc[#Data],tblAlloc[Parent]=$B$4),SEQUENCE(ROWS(FILTER(tblAlloc[#Data],tblAlloc[Parent]=$B$4))),{10,1,2,4})),{2,3,1}))
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands