compile data base on hit

[kokhong]

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..

 

[Max]

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

 

[kokhong]

Max, i only have three line here, there are A, B, C columns. A is IP, B is
username, and C is hits. I need the formula which can return IP and unique
username with highest hit..From ur example, it is not match with mine, i do
not hav G2.Thank in ur advance..

 

[Max]

The uniques list was one of my stated assumptions. You may already have a
ready list of all usernames, no? If not, you could use eg: advanced filter on
col B (check: uniques) to list out the uniques from col B (ie unique
usernames) into an empty col to the right of your source data, then copy that
uniques list n paste special as values into G2 down. Then apply the array
formulae given earlier - which address what I thought was more critical in
your query, adjusting the ranges to suit your actual data extents.

 

[kokhong]

Max, the advance filter option cannot filter only a row to unique record..a
message error will pop out if i do so.

 

[Max]

Select the entire username col before you do the advanced filter > uniques

 

[kokhong]

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?

 

[Max]

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.

 

[kokhong]

yes, from the information that you given. i manage to get the answer that i
want.thanks

 

[Max]

welcome, glad you did