Unique combinations from two columns

[DaveIsWorking]

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.

[HansV]

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})

[DaveIsWorking]

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.

[HansV]

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...)

[DaveIsWorking]

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.

[HansV]

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})

[DaveIsWorking]

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.

[HansV]

The formulas that I posted should do that...

[DaveIsWorking]

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:

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}))

I've saved this in my 'Memos' module in my PERSONAL.xlsb for quick reference, to copy and tweak wherever needed.

[HansV]

Glad it helped!