Prev: Function needed
Next: Nested subtotals in incorrect order on large worksheets
From: Alonso on 5 Feb 2010 10:20
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

From: Alonso on 5 Feb 2010 10:30
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
>
From: Herbert Seidenberg on 5 Feb 2010 15:42
Excel 2007 Tables
Most frequent combination.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_05_10.xlsx

From: Alonso on 5 Feb 2010 18:19
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.rackspacecloud.com/02_05_10.xlsx
>
> .
>
From: Dana DeLouis on 6 Feb 2010 22:54


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.rackspacecloud.com/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
 |  Next  |  Last
Pages: 1 2
Prev: Function needed
Next: Nested subtotals in incorrect order on large worksheets