Ideas for a counting combinations in a cross tab

Question

Hello, can someone direct me towards a solution for counting the number of combinations in this kind of data?  Expected output would be that 2 customers ordered Shoes and Pants, 1 customer odered Pants, Jeans and Shirts, etc.

Customer IDShoesPantsJeansShirts32111  12311  456 11178911  101112   1678 1 15431   0211   555111

Sample data attached.

Thanks,

Example Data.xlsx

DataBlender · Accepted Answer

Hi @mattlukoff, how about something like the attached?

It transposes, filters out nulls, sorts, before cross-tabbing and counting the occurrences.

JoeM · Accepted Answer

What a fun question! Attached is my approach:

1) Pivot the data so it may be concatenated

2) Did a data cleanse to clean up the whitespace in the table (due to how I copied it into my workflow)

3) Found a named combo for each CustID by concat'ing the named of the purchased items together

4) Counted the combos in the dataset