# Count the quantity of unique combinations of multiple columns in Excel

I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:

``````A  1
A  1
A  2
A  2
A  2
B  1
B  1
B  2
``````

Should result in:

``````A  1  2
A  2  3
B  1  2
B  2  1
``````

I did it very easily yesterday by importing into a SQL table, but I need to make it so anyone can do it, simply by placing those two columns in a spreadsheet. It should then populate a second sheet with the quantities table.

-
In the first set of example data should there be a `3` somewhere? Where did the `3` in the results come from? – Brad Aug 15 '12 at 16:24
Additionally you ask if you can find `how many times each combination shows up` but the example results show all the results. Would you like a single number representing all the possible combinations of would you like to see those combinations? – Brad Aug 15 '12 at 16:25
@Brad - The 3 is because the combination `A 2` shows up three times. – SaintWacko Aug 15 '12 at 16:51

Edit: Simple solution (credit goes to SaintWacko)

Concatenate the two columns:

``````=\$A2&\$B2
``````

Create a pivot table, using the concatenated column as a 'Row Label' and a 'Value'. Then make sure the 'Value' is calculating counts (should be default).

Complicated solution

You can concatenate the two columns in column C:

``````=\$A2&\$B2
``````

In column D, you'll need a function that determines if the item is a duplicate of another item so that the value is true for only one of each item (this only works if the items are sorted by the data columns, but the macro later does that).

``````=\$C1<>\$C2
``````

Then calculate the count if matched in column E:

``````=COUNTIF(\$C:\$C,\$C2)
``````

A macro will find all combinations for you, place them on sheet 2 and copy the counts. The code below makes some assumptions, but you can probably modify it to do what you need.

``````Option Explicit

Sub GetCombinations()

Dim sheet1, sheet2 As Worksheet
Set sheet1 = Worksheets(1)
Set sheet2 = Worksheets(2)

Dim sStartColumn As String
Dim iTopRow As Long
Dim sEndColumn As String
Dim iBottomRow As Long

sStartColumn = "A"
iTopRow = 1
sEndColumn = "E"
iBottomRow = sheet1.UsedRange.Rows.Count

Dim Rng As Range
Dim sRange1 As String
sRange1 = sStartColumn & CStr(iTopRow) & ":" & sEndColumn & CStr(iBottomRow)

Set Rng = sheet1.Range(sRange1)

Rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _

Dim i, j As Integer

j = 2

For i = 2 To iBottomRow

If sheet1.Cells(i, 4) Then

sheet2.Cells(j, 1) = sheet1.Cells(i, 1)
sheet2.Cells(j, 2) = sheet1.Cells(i, 2)
sheet2.Cells(j, 3) = sheet1.Cells(i, 5)
j = j + 1

End If

Next i

End Sub
``````

That should do it.

-
I would be happy with a macro to do it. I can't list out every combination, though. A, B, 1, 2, was just a greatly simplified example. It's actually combinations of zip codes and carrier routes. – SaintWacko Aug 15 '12 at 15:42
okay... give me a bit. I'll edit my answer – gregsdennis Aug 15 '12 at 15:42
You may have something there with that concatenation, however. I can count the quantity of each unique value for a single column. Then the only problem is selecting only a single entry for each unique value. – SaintWacko Aug 15 '12 at 15:54
Why do you need a list of all the combinations? Why not just search for how many times each combination appears? – soandos Aug 15 '12 at 15:55
Another sheet in the spreadsheet generates some sort of tag that gets printed out and used in shipping. I don't know the exact details, someone else wrote the code that generates the tag. I just need to be able to supply a sheet with three columns: Zip code, Carrier route, and the quantitiy of each combination of the two. – SaintWacko Aug 15 '12 at 15:58