Group data based on components

leebauman · Mar 6, 2016

My data contains information on the components inside hundreds of products. Column A contains product number. Column B contains a row for each component within the product and Column C contains a row to indicate the quantity of that component inside the product.

I know how many individual products I have, but I don't know how many different variations of components there are.

Example (sample data with desired results below):
Product AAA has 1 blueberry and 2 strawberries
Product BBB has 1 blueberry and 1 strawberry
Product CCC has 1 blueberry and 2 strawberries
Product DDD has 1 blueberry and 3 strawberries
Product EEE has 1 blueberry and 2 strawberries
Product FFF has 1 blueberry and 1 strawberry

So, there are:
3 combinations of 1 blueberry and 2 strawberries (50% of total)
2 combinations of 1 blueberry and 1 strawberry (33% of total)
1 combination of 1 blueberry and 3 strawberries (17% of total)

How can I use Excel to produce this type of analysis/outcome when there are thousands of data points? Any assistance is greatly appreciated!

<a href=http://www.filedropper.com/sampledata2><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files online</a></div>

leebauman · May 26, 2016

I'm resubmitting this thread with a more clear sample data set (below).

Data set contains order detail information from my yogurt store. Data includes order #, the ingredients of each order and the quantities of each ingredient (sample below).

I want to know the "commonness" within the orders. Which combination of ingredients in like quantities appear in orders most frequently and least frequently?

For example, it is possible that the combination Qty 1 Strawberry, Qty 2 Pears, Qty 1 Mango makes up 25% of my orders. I want to see this breakdown for each order (sample desired output also below).

Thanks!!

Sample Data:
Excel 2010 32 bit

	A	B	C
1	Order #	Ingredient	Quantity
2	44097	Almonds	1
3	44097	Apple	4
4	44097	Banana	1
5	44097	Blackberry	1
6	44097	Blueberry	1
7	44097	Blueberry	1
8	44097	Caramel	1
9	44097	Cereal	1
10	44097	Chocolate	1
11	44097	Gummy Bear	1
12	44097	Kiwi	2
13	44097	Kiwi	2
14	44097	Kiwi	2
15	44097	Lime	1
16	44097	Milk	1
17	44097	Milk	1
18	44097	Nectarine	1
19	44097	Orange	1
20	44097	Peanut Butter	1
21	44097	Pecans	1
22	44097	Plum	1
23	44097	Plum	1
24	44097	Raw Sugar	1
25	44097	Tangerine	1
26	44097	Vanilla	1
27	44097	Vanilla	1
28	44097	Walnuts	1
29	44097	Watermelon	1
30	44097	Watermelon	1
31	44097	Watermelon	1
32	45580	Apple	4
33	45580	Banana	1
34	45580	Banana	2
35	45580	Banana	1
36	45580	Blackberry	1
37	45580	Cantaloupe	1
38	45580	Caramel	1
39	45580	Cinnamon	1
40	45580	Grape	1
41	45580	Gummy Bear	1
42	45580	Kiwi	2
43	45580	Lime	1
44	45580	Orange	1
45	45580	Peanut Butter	2
46	45580	Pineapple	1
47	45580	Raw Sugar	1
48	45580	Tangerine	1
49	45580	Walnuts	1
50	45580	Watermelon	1

Sheet: Sheet1

Sample Desired Output:
Excel 2010 32 bit

	A	B	C
1	Ingredient Group	Quantity of individual ingredients	% of Grand Total ingredient groups
2	Strawberry	1	25%
3	Pear	2
4	Mango	1
5	Strawberry	2	8%
6	Nectarine	1
7	Almond	1
8	Blueberry	3

Sheet: Sheet2

leebauman · May 27, 2016

Hello, I'm not getting responses here, so I thought I'd try to simplify my question.

Below is my data set. There are 6 individual orders described here (order "a" through order "f") and each order has a different configuration of ingredients (ingredient types and quantities):
Excel 2010 32 bit

	A	B	C
1	Order	Ingredient	Quantity
2	a	apple	1
3	a	orange	5
4	b	banana	3
5	b	apple	2
6	b	orange	3
7	c	apple	1
8	c	orange	5
9	d	apple	2
10	d	banana	3
11	d	orange	3
12	e	banana	1
13	e	kiwi	1
14	e	lime	2
15	e	orange	1
16	f	orange	3
17	f	banana	3
18	f	apple	2

Sheet: Sheet1

And, here is my desired output. I want to summarize the orders by ingredient type and quantity:
Excel 2010 32 bit

	E	F	G	H
1	Ingredient	Quantity	Group Name	Quantity of Group
2	apple	1	ABC	2
3	orange	5
4	banana	3	ZZZ	3
5	apple	2
6	orange	3
7	banana	1	BBB	1
8	kiwi	1
9	lime	2
10	orange	1

Sheet: Sheet1

Is this possible? Please let me know if you have questions so I can better explain.

I appreciate the help!!

Group data based on components

leebauman

Board Regular

Excel Facts

leebauman

Board Regular

leebauman

Board Regular

Similar threads

Forum statistics

Share this page

Group data based on components

leebauman

Board Regular

Excel Facts

leebauman

Board Regular

leebauman

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock