Finding the combination that appears more times

A

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
 
A

Alonso

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

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
 
B

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top