Group data based on components

[leebauman]

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]

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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

Order #​

[/td][td]

Ingredient​

[/td][td]

Quantity​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

44097​

[/td][td]Almonds[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

44097​

[/td][td]Apple[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

44097​

[/td][td]Banana[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

44097​

[/td][td]Blackberry[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

44097​

[/td][td]Blueberry[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

44097​

[/td][td]Blueberry[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

44097​

[/td][td]Caramel[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

44097​

[/td][td]Cereal[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

44097​

[/td][td]Chocolate[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

44097​

[/td][td]Gummy Bear[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

44097​

[/td][td]Kiwi[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

44097​

[/td][td]Kiwi[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

44097​

[/td][td]Kiwi[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]

44097​

[/td][td]Lime[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]

44097​

[/td][td]Milk[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]

44097​

[/td][td]Milk[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]

44097​

[/td][td]Nectarine[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]

44097​

[/td][td]Orange[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td]

44097​

[/td][td]Peanut Butter[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

21​

[/td][td]

44097​

[/td][td]Pecans[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

22​

[/td][td]

44097​

[/td][td]Plum[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

23​

[/td][td]

44097​

[/td][td]Plum[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

24​

[/td][td]

44097​

[/td][td]Raw Sugar[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

25​

[/td][td]

44097​

[/td][td]Tangerine[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

26​

[/td][td]

44097​

[/td][td]Vanilla[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

27​

[/td][td]

44097​

[/td][td]Vanilla[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

28​

[/td][td]

44097​

[/td][td]Walnuts[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

29​

[/td][td]

44097​

[/td][td]Watermelon[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

30​

[/td][td]

44097​

[/td][td]Watermelon[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

31​

[/td][td]

44097​

[/td][td]Watermelon[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

32​

[/td][td]

45580​

[/td][td]Apple[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

33​

[/td][td]

45580​

[/td][td]Banana[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

34​

[/td][td]

45580​

[/td][td]Banana[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

35​

[/td][td]

45580​

[/td][td]Banana[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

36​

[/td][td]

45580​

[/td][td]Blackberry[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

37​

[/td][td]

45580​

[/td][td]Cantaloupe[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

38​

[/td][td]

45580​

[/td][td]Caramel[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

39​

[/td][td]

45580​

[/td][td]Cinnamon[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

40​

[/td][td]

45580​

[/td][td]Grape[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

41​

[/td][td]

45580​

[/td][td]Gummy Bear[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

42​

[/td][td]

45580​

[/td][td]Kiwi[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

43​

[/td][td]

45580​

[/td][td]Lime[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

44​

[/td][td]

45580​

[/td][td]Orange[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

45​

[/td][td]

45580​

[/td][td]Peanut Butter[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

46​

[/td][td]

45580​

[/td][td]Pineapple[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

47​

[/td][td]

45580​

[/td][td]Raw Sugar[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

48​

[/td][td]

45580​

[/td][td]Tangerine[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

49​

[/td][td]

45580​

[/td][td]Walnuts[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

50​

[/td][td]

45580​

[/td][td]Watermelon[/td][td]

1​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Sample Desired Output:
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Ingredient Group[/td][td]Quantity of individual ingredients[/td][td]% of Grand Total ingredient groups[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Strawberry[/td][td]

1​

[/td][td]

25%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Pear[/td][td]

2​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Mango[/td][td]

1​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]Strawberry[/td][td]

2​

[/td][td]

8%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]Nectarine[/td][td]

1​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]Almond[/td][td]

1​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]Blueberry[/td][td]

3​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[leebauman]

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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

Order​

[/td][td]

Ingredient​

[/td][td]

Quantity​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]a[/td][td]apple[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]a[/td][td]orange[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]b[/td][td]banana[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]b[/td][td]apple[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]b[/td][td]orange[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]c[/td][td]apple[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]c[/td][td]orange[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]d[/td][td]apple[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]d[/td][td]banana[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]d[/td][td]orange[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]e[/td][td]banana[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]e[/td][td]kiwi[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]e[/td][td]lime[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]e[/td][td]orange[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]f[/td][td]orange[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]f[/td][td]banana[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]f[/td][td]apple[/td][td]

2​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

And, here is my desired output. I want to summarize the orders by ingredient type and quantity:
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

Ingredient​

[/td][td]

Quantity​

[/td][td]

Group Name​

[/td][td]

Quantity of Group​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]apple[/td][td]

1​

[/td][td]

ABC​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]orange[/td][td]

5​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]banana[/td][td]

3​

[/td][td]

ZZZ​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]apple[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]orange[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]banana[/td][td]

1​

[/td][td]

BBB​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]kiwi[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]lime[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]orange[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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

I appreciate the help!!