Help with duplicate values

[ironj32]

Hopefully I can illustrate my problem without confusing everyone!
Here's my situation: I have a worksheet which looks like below.
ColA ColB
1 Red
2 Red
3 Red
4 Dog
5 Dog
6 Blue
7 Blue
8 Green
9 Green

Is there a formula I can use to extract the 1st row of each duplicate set (column A having unique values, column B having duplicates)? So from above my result would be:
ColA ColB
1 Red
4 Dog
6 Blue
8 Green

[mohitspamz]

In colC
C2 =IF(B2=B1,"Repeat","First Value")

Then u cud use autofilter and delete all rows having "Repeat"

Hope this helps

Regards
Mohit

[NBVC]

If you have an empty row above, then in C2:

=IF(B1=B2,"",MAX($C$1:C1)+1)

copied down will indicate first record

then, say in E2,

=INDEX(A$2:A$10,MATCH(ROWS($E$2:$E2),$C$2:$C$10,0)) copied to next column and down equivalent number of rows as there are different categories...

See attached.

[ironj32]

Thanks! I ended up using NBVC's method...works like a charm.

[EXCEL_NEWBI]

Hello, I have a similar problem but I would like to extract whole row and also add sum of 'number of times repeated'

Example:
ColA ColB ColC
1 Red 12/7
2 Red 12/7
3 Red 12/6
4 Dog 12/7
5 Dog 12/7
6 Blue 12/7
7 Blue 12/7
8 Green 12/7
9 Green 12/7
10 Green 12/6

Result to be:
ColA ColB ColC ColD
1 Red 12/7 2
3 Red 12/6 1
4 Dog 12/7 2
6 Blue 12/7 2
8 Green 12/7 2
10 Green 12/6 1

Thank you

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.