unique values in Excel

N

nikos

Hi to everyone.
I have 3 columns in an Excel sheet (lets say A,B and C). The first and third
have unique values. The second could have non unique values.
I would like from these columns to extract the corresponding columns, in a
way to include unique values.
Here is a simple example.

The first table:

A B
C

1 B1
C1
2 B2
C2
3 B3
C3
4 B3
C4
5 B3
C5
6 B6
C6
7 B7
C7
8 B7
C8
9 B9
C9
10 B10
C10

and the final table which must extract is:

A B
C

1 B1
C1
2 B2
C2
3 B3
C3
4 B6
C6
5 B7
C7
6 B9
C9
7 B10
C10

Thank you.

N

nikos

Sorry a copy paste error. The B column is in C place.

M

Max

Assume source data as posted in A1:C10
In D1: =IF(COUNTIF(C\$1:C1,C1)>1,"",ROW())
In E1: =IF(ROW()>COUNT(\$D:\$D),"",INDEX(A:A,SMALL(\$D:\$D,ROW())))
Copy E1 to G1. Select D1:G1, copy down to G10. Minimize/hide col D. Cols E
to G returns what you seek, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

N

nikos

thank you.

Max said:
Assume source data as posted in A1:C10
In D1: =IF(COUNTIF(C\$1:C1,C1)>1,"",ROW())
In E1: =IF(ROW()>COUNT(\$D:\$D),"",INDEX(A:A,SMALL(\$D:\$D,ROW())))
Copy E1 to G1. Select D1:G1, copy down to G10. Minimize/hide col D. Cols E
to G returns what you seek, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik