Excel: How to assign values to unique combinations/check for unique combinations in large data sets

0
votes

I'm analyzing sales data in an Excel report. Each row represents a product sold.

ticket          product 
20190101203045  Eggs
20190101203045  Pancakes
20190101203045  Happy meal
20190101203045  Coca-Cola
20190101203045  Orange juice
20190102144525  All-Bran cereal
20190102144525  Fruit
20190102144526  Lemonade

I have successfully concatenated all products with the same ticket # and separated them into columns:

20190101203045,Eggs,Pancakes,Happy meal,Coca-Cola,Orange juice
20190102144525,All-Bran cereal,Fruit
20190102144526,Lemonade

To test for unique combinations (no particular order) I used a simple and effective approach, assign a power of 2 (1,2,4,8,16,...) number to every individual value (eggs, pancakes, happy meal, ...) so that every unique combination results in a unique value when adding up their assigned values, example:

20190101203045,1,2,4,8,16       = 31  (only this combination will result in 31)
20190102144525,32,64            = 96  (only this combination will result in 96)
20190102144526,128              = 128 (only this combination will result in 128)

This works very well with a small number of values, I would just SUM these values and search for duplicates, but the files I'm analyzing may have up to 2000 different values (products), so this approach no longer works (numbers get BIG).

There are other approaches like using formulas, but the sheets have thousands of rows (too much processing), so I'm looking for a more creative, efficient solution.

Any help is appreciated

1
excelexcel-formulacombinations
Sort the Products before you compare. - Ron Rosenfeld
The two formulas I use to concatenate (put the values in a single row) require data to be sorted by column A (ticket). I can first sort by column A (ticket) and then by column B (products) before doing that, but I don't see how it would help. - nostromo1856
Huh? 20190101203045,Eggs,Pancakes,Happy meal,Coca-Cola,Orange juice does not look as if the product list is sorted. - Ron Rosenfeld
Sorry, I now understand what you are suggesting: If I sort first by column A and then by column B (custom filtering), and then I apply the formula to concatenate, I should end up with matching rows, irrespective of the order the items show up in the original data set. Thank you, this will definitely help! - nostromo1856

1 Answers

1
votes

From the description of your approach, I believe you can easily do this with Power Query, but it may take some calculation time.

Try to visualize this...

  • Load your data (2 columns: ticket #, item)

  • Pivot by column ticket number

  • Remove column ticket number

  • Remove duplicate rows

  • Add new index column, call it Unique Sales

Now you're done. Each row is a unique combination.

(Edit) originally recommended unpivoting as a final step (oops) don't do that.