Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

up vote 3 down vote accepted

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, _
             Orientation:=xlSortColumns, Header:=xlYes

    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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.