From: Max on 2 Apr 2010 03:37 Select the entire username col before you do the advanced filter > uniques -- Max Singapore --- "kokhong" wrote:> Max, the advance filter option cannot filter only a row to unique record..a > message error will pop out if i do so. From: kokhong on 2 Apr 2010 04:51 after filter with uniques unit and copy the columns B to G, the original B columns need to filter it to all, or keep it in uniques value? and since i dun hav data in column D but C should i edit the formula lik below? and since i have 14706 row, should it be below formula? > In H2, array enter (press CSE): > =MAX(IF(\$B\$2:\$B\$14706=G2,\$C\$2:\$C\$14706)) > In I2, array enter (press CSE): > =INDEX(\$A\$2:\$A\$10,MATCH(H2,IF(\$B\$2:\$B\$14706=G2,\$C\$2:\$C\$14706),0)) "Max" wrote: > Assume your data as posted in cols A to D, where > col A = misc letters, > col B = items, eg A, B, > col D = numbers > Assume listed in G2 down are the unique items from col B: A, B, etc. > Then > In H2, array enter (press CSE): > =MAX(IF(\$B\$2:\$B\$10=G2,\$D\$2:\$D\$10)) > In I2, array enter (press CSE): > =INDEX(\$A\$2:\$A\$10,MATCH(H2,IF(\$B\$2:\$B\$10=G2,\$D\$2:\$D\$10),0)) > Copy H2:I2 down. H2 returns the desired maximum number from col D for the > unique item listed in G2, I2 returns the corresponding misc letter from col > A. > CSE means to press CTRL+SHIFT+ENTER to confirm the formula. Visually check > the formula bar for the curlies: { } inserted by Excel which confirms that > the CSE was properly done. If you don't see the { }, re-do the CSE. > Inspiring? hit YES below > -- > Max > Singapore > --- > "kokhong" wrote: > > Before compile the data is: > > IP user name Hits > > x A 10 > > y A 1000 > > Z A 20 > > L B 2000 > > M B 10 > > N B 500 > > > > After compile the data is: > > IP user name Hits > > y A 1000 > > L B 2000 > > > > are there any script can choose the username (Column B) base on the highest > > hits at columns C. Thank you.. > > From: Max on 2 Apr 2010 10:57 > after filter with uniques unit and copy the columns B to G, the original B > columns need to filter it to all, or keep it in uniques value? Copy the uniques n paste it into G2 down. I did not say to delete or overwrite col B > and since i dun hav data in column D but C should i edit the formula lik > below? and since i have 14706 row, should it be below formula? Yes, of course, change it to suit As for the adaptations, this index bit> INDEX(\$A\$2:\$A\$10 needs to be changed as well to > INDEX(\$A\$2:\$A\$14706 Don't forget to ensure that the CSE bit is done properly. -- Max Singapore --- From: kokhong on 5 Apr 2010 04:07 yes, from the information that you given. i manage to get the answer that i want.thanks "Max" wrote: > > after filter with uniques unit and copy the columns B to G, the original B > > columns need to filter it to all, or keep it in uniques value? > > Copy the uniques n paste it into G2 down. I did not say to delete or > overwrite col B > > > and since i dun hav data in column D but C should i edit the formula lik > > below? and since i have 14706 row, should it be below formula? > > Yes, of course, change it to suit > > As for the adaptations, this index bit > > INDEX(\$A\$2:\$A\$10 > > needs to be changed as well to > > INDEX(\$A\$2:\$A\$14706 > > Don't forget to ensure that the CSE bit is done properly. > -- > Max > Singapore > --- > From: Max on 5 Apr 2010 19:22 welcome, glad you did -- Max Singapore "kokhong" wrote in message news:B77A0AF4-C5C3-4933-B405-8A81D1FC89BF(a)microsoft.com...> yes, from the information that you given. i manage to get the answer that > i > want.thanks