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>

 

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

#					NUMBER OF TIMES COMBINATION APPEARS
1	Dumplings	Kung Pao Shrimp	Fried rice	Fortune Cookie	50
2	Dumplings	Kung Pao Shrimp	Fried rice	Ice Cream	12
3	Dumplings	Kung Pao Shrimp	Steamed rice	Fortune Cookie	11
4	Dumplings	Kung Pao Shrimp	Steamed rice	Ice Cream	9
5	Dumplings	Kung Pao Shrimp	Rice noodles	Fortune Cookie	10
6	Dumplings	Kung Pao Shrimp	Rice noodles	Ice Cream	3
7	Dumplings	Orange Beef	Fried rice	Fortune Cookie	2
8	Dumplings	Orange Beef	Fried rice	Ice Cream	2
9	Dumplings	Orange Beef	Steamed rice	Fortune Cookie	1
10	Dumplings	Orange Beef	Steamed rice	Ice Cream	1
11	Dumplings	Orange Beef	Rice noodles	Fortune Cookie	5
12	Dumplings	Orange Beef	Rice noodles	Ice Cream	5

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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)