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 neededNext: Nested subtotals in incorrect order on large worksheets