Viewing Products Purchased by Order History

[Jday324]

Hey guys,

I have tried finding this on the Internets, but after a few hours I am getting no where.

I have an Excel file that list Customer ID, Order ID, Quantity, and Color. You can see an example below.

<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC; } .tableizer-table th { background-color: #104E8B; color: #FFF; font-weight: bold; }</style>[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Customer ID[/TH]
[TH]Order ID[/TH]
[TH]Quantity[/TH]
[TH]Color[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111111[/TD]
[TD]4[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111111[/TD]
[TD]1[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111112[/TD]
[TD]3[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111113[/TD]
[TD]4[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222221[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222221[/TD]
[TD]3[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222222[/TD]
[TD]1[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222223[/TD]
[TD]3[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]


My goal is to understand what colors our customers are buying by order history. This would help us identify if there is a drop off at a specific color, that would allow us to run promotions on a more popular color. I would need to calculate this order history by color. See an example below.

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Order 1[/TH]
[TH]Order 2[/TH]
[TH]Order 3[/TH]
[/TR]
[TR]
[TD]Black, Blue[/TD]
[TD]Black[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue, Orange[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Blue[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]

The problem is the calculation. How do I identify a customer, run through each of their orders, and then pull each color into a order history? I can then run the data through a pivot table.

Any help would be MUCH appreciated!

Bonus points for an answer that also takes into Quantity.

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Customer ID[/TH]
[TH]Order ID[/TH]
[TH]Quantity[/TH]
[TH]Flavor[/TH]
[TH]Order 1[/TH]
[TH]Order 2[/TH]
[TH]Order 3[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111111[/TD]
[TD]4[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111111[/TD]
[TD]1[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111112[/TD]
[TD]3[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500111113[/TD]
[TD]4[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222221[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222221[/TD]
[TD]3[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222222[/TD]
[TD]1[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500222223[/TD]
[TD]3[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500333331[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]