Finding the combination that appears more times

[Alonso]

Hi everybody
i have a list on excel 2007 that displays the purchase of items on columns
A:E, each column showing one (1) item
I want to find what combination of items appears more times, especifically,
which combination of four (4) is the favorite mix

eg:
A B C D F
A C D E F
B C D E F
B C D F Z

in this example, the winner would be (B, C, D, E) as it appears 3 times
the main problem is that I have over 1,000 rows
and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination
would take forever...

is there a simpler, quicker way to do this??
i don´t know if concatenate, because the items can be on any column

 

[Alonso]

Typho error, the winner would be (B, C, D, F)

 

[Herbert Seidenberg]

Excel 2007 Tables
Most frequent combination.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_05_10.xlsx

 

[Alonso]

Thaks Herbert
seems interesting, let me try to understand it and get back to you

 

[Dana DeLouis]

Thaks Herbert
seems interesting, let me try to understand it and get back to you

As a side note, if you want to look into it further, I would Rank each
subset. If we assume there are 26 distinct items, a macro would first
adjust each list into integers (perhaps Asci code of the letters)

For example, your last example would be:
"BCDFZ"

{2, 3, 4, 6, 26}

Look at each of the 5 subsets...

{2, 3, 4, 6}
{2, 3, 4, 26}
{2, 3, 6, 26}
{2, 4, 6, 26}
{3, 4, 6, 26}

With 26 items the upper size is
=Combin(26,4) = 14,950

The above five values would be:

{2302, 2322, 2363, 2594, 4365}

The number 2302 would show up the most. (I would use a Dictionary object)

To get the value of this number would be

? UKS(2302, 4, 26)

{2, 3, 4, 6}

Which when reversed would be "B C D F"

A macro for this is very fast.
Again, it might be something you might want to research.

Dana DeLouis

 

[Bernd P]

Hello,

I think it's a bit early to provide "solutions".

How many different items does Alonso really have and with how many can
his "mean boss" come up with in future? The supposed answer 26 seems
quite unlikely to me.

And is the number 4 for the favourite mix likely to be changed, again
maybe by his "mean boss"?

If the number of all possible combinations you may find that you
should start with frequency tables of
a) most frequently purchased single items
b) most frequent mix of 2
c) ... of 3
and so on, for n maybe only exploring the more likely mixes < (1),
(n-1) >

Regards,
Bernd

 

[Alonso]

Actually, I'm open to any kind of possible "solutions"

As Bernd says, it likely that somewhere in the future I'll need to check the
top 2 mix, the top 3 mix and so on

right now I'm working with almost 60 items (and it's difficult to increase
this since 8-10 items doesn't sell very well)
I can easily turn each item into numbers with a vlookup

 

[Alonso]

Dana

could you provide an example for this macro??

 

[Herbert Seidenberg]

Excel 2007 Tables
Most frequent combinations
Revised and expanded.
No macro.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_05_10.xlsx

 

[Alonso]

Amazing...
thanks!!