Combinations formula

[jawad93]

Hi there,

Need some help with a formula for combinations of items.

I have a list of items that are in groups like below:

[TABLE="width: 112"]
<colgroup><col width="56" span="2" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]Group[/TD]
[TD="width: 56"]Item[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Kiwi[/TD]
[/TR]
</tbody>[/TABLE]

And need a formula (without macros ideally) that can provide an output that has all the different combinations of items from different groups (the order they appear in doesn't matter):
[TABLE="width: 168"]
<colgroup><col width="56" span="3" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]A[/TD]
[TD="width: 56"]B[/TD]
[TD="width: 56"]C[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pear[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Banana[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Banana[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Am open to formatting the sheets differently as well, any advice would be great. Thank you so much!

 

[Roderick_E]

good job for a pivot table

 

[jawad93]

good job for a pivot table

How would you solve this with a pivot table?