Number of unique customers for every possible product combination? (2007 CSE)

Hello Everyone:

[Because this post attracted no suggestions in the first 5 hours, I have simplified the problem and presented the simpler version in the second post, below. I think forum members should probably read and respond to the second post, before or instead of responding to the first. Cheers & genuine thanks!]


I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.

Here's what I mean (though I can't figure out how to create neat columns in this post):

[C1] [C2]

Prod1 CharlieCo
Prod3 AlphaCorp
Prod2 BetaInc
Prod3 BetaInc
Prod1 AlphaCopr


Thanks to this forum, I do know how to count the number of *unique* values in a column using a CSE formula. So, I can use that formula to calculate that there are 3 unique customers in the example above.

But now the challenge is harder. I need to calculate the number of *unique* clients who purchased every possible combination of products!

Let me say right away that I'm not looking for someone to come up with a CSE formula for every potential combination of products. Rather, I'd be enormously grateful if someone can show me an extensible way to calculate the number of distinct customers for *any particular* combination of products.

Also, it's no problem for me to manually identify all the possible combinations, like this:

Prod1 and Prod2 and Prod3
Prod1 and Prod2
Prod1 and Prod3
Prod2 and Prod3
Prod1
Prod2
Prod3

... so what I really need is for someone to show me how to calculate the answer for , say, "Prod1 and Prod2 and Prod3", and I should be able to do the rest!

BTW, the sum of these 7 calculations should still be 3, per the example above.

Of course, my actual situation is considerably more complicated, with about 80 possible product combinations. For that reason and others, I'd prefer not to introduce 80 new columns into my spreadsheet as an intermediate step to solving this problem.

So, can anyone come up with an efficient solution??

CSE is fine.

Cheers,

Jay

Column 3:

add this code to a new module in the VB editor (Alt+F11)

Please Login or Register  to view this content.

then assuming your data is in A1:B5, use:

=SUBSTITUTE(TRIM(aconcat(IF($B$1:$B$5=B1,$A$1:$A$5,"")," "))," ",",")

and confirm with CTRL+SHIFT+ENTER not just ENTER

copy down...

This separate entries with comma, you can change that... by changing the symbol between double quotes in last part of the formula.

You are most welcome...

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save