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: 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" <kokhong(a)discussions.microsoft.com> 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


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