Finding combinations and counting them in Excel

Question

I don't know much about Excel and I'm trying to do the following:

So, if a I had column A and column B:

A     B  
red   green  
red   green  
red   green  
blue  pink  
blue  pink  
blue  pink  
blue  pink  
black white  
black white  

Let's say I have hundreds of rows of combinations. What I need to do is on a second sheet, show all the different combinations and the number of times each occurs. So for the above, the result would be:

Combination: Number of times:   
red green    3  
blue pink    4  
black white  2  

So, I would need to give me the combination and the number of times it occurs. Any idea how I could do this?

Solution 1

Make a header into your spreadsheet: A1 = color1, B1 = color2, C1 = combination

1- Type on C2

=A2&"-"&B2

drag the formula down on column C until the last row in which there are data on columns A and B.

2- Go to "Insert" --> "PivotTable"

Drag "combination" into the "Row Labels", and Drag "combinations" into the "Values" label.
You need to have a mathematical operation in the pivot-table "Values" field, and the "Count" operation is already set automatically when one drags a variable into it (so, it should appear "Count of combinations").

Here is a screenshot about how the Pivot Table should look like:

Solution 2

One way you could do this is the following:

Select the entire data, copy it and paste it where you want to calculate the number of occurences. Select that range and in the Data tab select Remove Duplicates. This will get you all unique occurences of patterns.

Now, with the following formula you can get the count of each of those cases. Notice that this is an array formula so when you enter it initially you have to hit Ctrl+Shift+Enter in the formula box for it to calculate properly. Here's the formula, just change the cells to those that match your need:

=SUM(IF($A$1:$A$4&$B$1:$B$4=A1&B1,1,0))

Here,$A$1:$A$4&$B$1:$B$4 concatenates the two columns together to create "keys". It then matches this with the current combination to check (A1&B1) and then returns 0 or 1 and sums the total to get the count.

Comments

[Jona]

Thank you so much!!! It works great! Just a random question: would it possible to also show which rows have a certain combination? So for example if red-green is in rows 2-20 and 200-500, would it be possible to show that? Most similar combinations seem to be grouped together. You seem to know excel so I'm just asking but don't worry if there isn't. I really appreciate your help.

[Andre Silva]

I am not sure if I understood your question, but you might try to filter your data (go to data-->filter), and filter the data in the main spreadsheet by column C (combination) values (select the combination you want to display). You can also sort (go to Home-->Sort&Filter-->CustomSort--->sort by = column C). I do not know excel, so much, but that one I could help. I need to go now. Thanks.