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

[TABLE="width: 1034"]
<tbody>[TR]
[TD]Combination name [/TD]
[TD]Combination content [/TD]
[TD]Number of times appeared[/TD]
[/TR]
[TR]
[TD]Combination 1[/TD]
[TD] iron; 1; option 1.moves up; yes; regular; height and width adjustment; synchroon blokkering elke posi; partial[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Combination 2[/TD]
[TD]iron, plastic; 1; option 1, moves up; no; regular; height adjustment; synchroon blokkering elke posi; double[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Combination 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Combination 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 858"]
<tbody>[TR]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[TD]Option 4[/TD]
[TD]Option 5[/TD]
[TD]Option 6[/TD]
[TD]Option 7[/TD]
[TD]Option 8[/TD]
[/TR]
[TR]
[TD]iron[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]yes[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 1, moves down[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 2, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]2[/TD]
[TD]option 1, moves down[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 2, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]special[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]3[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]3[/TD]
[TD]option 5[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 6[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 7[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 8[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron[/TD]
[TD]3[/TD]
[TD]option 9[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Does this help:

https://app.box.com/s/47b28f19d794b25511be

?

 

[snezana]

Does this help:

https://app.box.com/s/47b28f19d794b25511be

?

Hi, sorry for the late reply I shut down my computer early yesterday

From what i can gather this looks like something where you input the possible options, and it gives you back a list of possible combinations, right?

If so, what I want looks like this but is not the same. I already have a list of combinations. I have a list of let's say 100 rows, which is 100 customers buying a certain product but these products have different extra's added to them. From this list of 100 rows I want to have a smaller list returned that shows me what the common combinations are. So, which combination of options are people buying? Then, it should add to this list a count of how many times this combination of option appears.

So output, if we put it in the example of the chinese menu could be:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]#[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="width: 64"]NUMBER OF TIMES COMBINATION APPEARS[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Fried rice[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Fried rice[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Steamed rice[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Steamed rice[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Rice noodles[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Kung Pao Shrimp[/TD]
[TD="class: xl67"]Rice noodles[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Fried rice[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Fried rice[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Steamed rice[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Steamed rice[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Rice noodles[/TD]
[TD="class: xl67"]Fortune Cookie[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="class: xl67"]Dumplings[/TD]
[TD="class: xl67"]Orange Beef[/TD]
[TD="class: xl67"]Rice noodles[/TD]
[TD="class: xl67"]Ice Cream[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

And input would be the actual list of combinations that people have ordered. So 50 rows of combination #1, 12 rows of combination #2, etc...

 

[mole999]

how do you go from materials to food

 

[snezana]

how do you go from materials to food

Since someone replied with an example of food x)

 

[sheetspread]

how do you go from materials to food

Since someone replied with an example of food x)

That's why the macro is so great, you can substitute whatever you like for food, and add/remove extra items (try it and see!)

But yes, that doesn't solve your problem here. You want a conditional count of the combinations after filtering them with remove duplicates or the advanced filter. Am I right?

 

[snezana]

That's why the macro is so great, you can substitute whatever you like for food, and add extra items (try it and see!)

But yes, that doesn't solve your problem here. You want a conditional count of the combinations after filtering them with remove duplicates or the advanced filter. Am I right?

Yes that's what I'm looking for! Filtering with remove duplicates I can do, whatever comes next not so much...

 

[sheetspread]

Ok, countifs is pretty easy to write, but rather than have so many formulas on the page you might consider setting a pivot table which will filter and count in one shot.

 

[snezana]

Ok, countifs is pretty easy to write, but rather than have so many formulas on the page you might consider setting a pivot table which will filter and count in one shot.

How do I set the pivot to count all the combination, instead of the individual counts per option (so if i add the 8 options that are in 8 seperate columns, it counts the entries in the seperate columns, while i want a count of the combinations). I suspect this is not that difficult to do, but unfortunately I don't know..

 

[sheetspread]

Actually I'm not sure if you can count identical row occurences in a pivot table. PowerPivot/Power Query may be able to (SQL can), and Excel 2013 has a new distinct count function but that may not apply here. The countifs formula goes like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Name	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8
2	Q	1	A	AQW	QEM	MTU	UPL	FGP	FLH
3	W	1	A	MMJ	AOW	WDP	JGC	PBT	RXF
4	E	3	A	WIS	EVY	QXO	ATB	JNP	TQW
5	R	2	S	KAM	CNU	VKO	OYR	MMF	UKQ
6	T	2	S	IXD	USJ	GGN	SND	RFH	PTD
7	Q	1	A	AQW	QEM	MTU	UPL	FGP	FLH
8	Q	2	A	CDX	GLO	VDU	RLJ	BKZ	AFT
9	E	1	A	UGT	HYD	AAB	SBO	KTH	GRL
10	R	2	S	KAM	CNU	VKO	OYR	MMF	UKQ
11	R	2	S	KAM	CNU	VKO	OYR	MMF	UKQ
12
13
14	Name	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8
15	Q	1	A	AQW	QEM	MTU	UPL	FGP	FLH	2
16	W	1	A	MMJ	AOW	WDP	JGC	PBT	RXF	1
17	E	3	A	WIS	EVY	QXO	ATB	JNP	TQW	1
18	R	2	S	KAM	CNU	VKO	OYR	MMF	UKQ	3
19	T	2	S	IXD	USJ	GGN	SND	RFH	PTD	1
20	Q	2	A	CDX	GLO	VDU	RLJ	BKZ	AFT	1
21	E	1	A	UGT	HYD	AAB	SBO	KTH	GRL	1
Sheet19

Cell Formulas
Range	Formula
J15	=COUNTIFS($A$2:$A$11,A15,$B$2:$B$11,B15,$C$2:$C$11,C15,$D$2:$D$11,D15,$E$2:$E$11,E15,$F$2:$F$11,F15,$G$2:$G$11,G15,$H$2:$H$11,H15,$I$2:$I$11,I15)

which requires each column to be typed (an mmult formula might let you enter a single range)