Excel challenge: Which combinations do columns have, and how many

[snezana]

Hi all,

So I have a nice challenge that I can't solve since I'm not a big excel expert.. hope someone can help me out!

So i have this product that is being sold. And a single product code (let's say the code is 0001) can have different entries in the columns that follow. E.g. if we sell a product with code 0001, there are 8 different extra options for that product that the consumer can buy. Sometimes people will choose the simple version of product 0001, but they can mix and match what they want added. e.g what type of material the product is made of, whether it can be adjusted yes or no, whether it has a expensive fabric or not... these options are reflected in columns 'Option 1' up until 'Option 8'.

Now what I need to find out is, for a particular product (here, 0001), which combinations of extra options appear, and how many times. I've made a hypothetical table below as an example to work with, but the actual data set would probably have 1000 rows for product 0001 alone.

As you can see there are many different options, and within those options there are a set number of options (which fortunately are always written in the same way so they are exact matches). I would like to create a list from this data saying, e.g.:

Combination name	Combination content	Number of times appeared
Combination 1	iron; 1; option 1.moves up; yes; regular; height and width adjustment; synchroon blokkering elke posi; partial	5
Combination 2	iron, plastic; 1; option 1, moves up; no; regular; height adjustment; synchroon blokkering elke posi; double	6
Combination 3
Combination 4
etc...

<tbody>
</tbody>

So, it should (1) automatically make a list of combinations to show me WHICH combinations there are in the first place, and (2) then count the number of times that combination appear in the data set. I wouldn't be surprised if there were 20 different combinations.

Option 1	Option 2	Option 3	Option 4	Option 5	Option 6	Option 7	Option 8
iron	1	option 1, moves up	yes	regular	height and width adjustment	synchroon blokkering elke posi	partial
iron, plastic	1	option 1, moves up	no	regular	height adjustment	synchroon blokkering elke posi	double
iron, plastic	1	option 1, moves up	no	regular	height adjustment	synchroon blokkering elke posi	none
other	1	option 1, moves down	no	regular	height and width adjustment	synchroon blokkering elke posi	none
other	1	option 1, moves up	no	regular	height and width adjustment	synchroon blokkering elke posi	none
other	1	option 2, moves up	no	regular	height and width adjustment	synchroon blokkering elke posi	none
other	2	option 1, moves down	no	regular	height and width adjustment	synchroon blokkering elke posi	none
soft	2	option 2, moves up	no	regular	height and width adjustment	synchroon blokkering elke posi	none
soft	2	option 1, moves up	no	regular	height and width adjustment	synchroon blokkering elke posi	none
soft	2	option 1, moves up	no	special	height and width adjustment	synchroon blokkering elke posi	partial
soft	2	option 3	yes	special	height adjustment	synchroon blokkering elke posi	partial
soft	2	option 3	yes	special	height adjustment	synchroon blokkering elke posi	partial
soft	3	option 3	yes	special	height adjustment	synchroon blokkering elke posi	partial
soft	3	option 5	yes	special	height adjustment	synchroon blokkering elke posi	partial
iron, plastic	3	option 6	yes	special	height adjustment	synchroon blokkering elke posi	double
iron, plastic	3	option 7	yes	special	height adjustment	synchroon blokkering elke posi	double
iron, plastic	3	option 8	yes	special	height adjustment	synchroon blokkering elke posi	double
iron	3	option 9	yes	special	height adjustment	synchroon blokkering elke posi	double

<tbody>
</tbody>

 

[snezana]

Thanks, I can work with this!

 

[sheetspread]

Ok, and if you're interested in the SQL method it's very easily done in Access with the query wizard, as well as Excel (Microsoft Query, which I learned about from Fazza, shown below):

which can appear in the spreadsheet as: