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

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

"Alonso" wrote:

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

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

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

"Herbert Seidenberg" wrote:

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

.

On 2/5/10 6:19 PM, Alonso wrote:
Thaks Herbert
seems interesting, let me try to understand it and get back to you

"Herbert Seidenberg" wrote:

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



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

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

Dana

could you provide an example for this macro??


"Dana DeLouis" wrote:



On 2/5/10 6:19 PM, Alonso wrote:
Thaks Herbert
seems interesting, let me try to understand it and get back to you

"Herbert Seidenberg" wrote:

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



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
.

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

Amazing...
thanks!!


"Herbert Seidenberg" wrote:

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