Prev: Function needed
Next: Nested subtotals in incorrect order on large worksheets
From: Bernd P on 7 Feb 2010 04:32
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
From: Alonso on 8 Feb 2010 10:00
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


"Bernd P" wrote:

> 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
> .
>
From: Alonso on 9 Feb 2010 11:47
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.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
> .
>
From: Herbert Seidenberg on 10 Feb 2010 19:52
Excel 2007 Tables
Most frequent combinations
Revised and expanded.
No macro.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_05_10.xlsx
From: Alonso on 12 Feb 2010 16:27
Amazing...
thanks!!


"Herbert Seidenberg" wrote:

> Excel 2007 Tables
> Most frequent combinations
> Revised and expanded.
> No macro.
> http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_05_10.xlsx
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Function needed
Next: Nested subtotals in incorrect order on large worksheets