compile data base on hit
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..
|