Prev: How do I parse the data in a cell
Next: Moving Text from Word to Excel (and having it set into 2 colum
From: kokhong on 1 Apr 2010 20:50
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 1 Apr 2010 21:57
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: kokhong on 1 Apr 2010 22:38
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..
> >
From: Max on 2 Apr 2010 02:30
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..

From: kokhong on 2 Apr 2010 03:05
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..
>
 |  Next  |  Last
Pages: 1 2
Prev: How do I parse the data in a cell
Next: Moving Text from Word to Excel (and having it set into 2 colum