compile data base on hit
 

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

compile data base on hit
 

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

compile data base on hit
 

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

compile data base on hit
 

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.
--
Max
Singapore
---
"kokhong" wrote:
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..

compile data base on hit
 

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

"Max" wrote:

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.
--
Max
Singapore
---
"kokhong" wrote:
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..

compile data base on hit
 

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.

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

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?

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

compile data base on hit
 

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

compile data base on hit
 

welcome, glad you did
--
Max
Singapore

"kokhong" wrote in message
...
yes, from the information that you given. i manage to get the answer that
i
want.thanks