Say we have in A1 thru A20:

a
a
b
c
d
d
e
f
g
g
g
h
h
i
j
j
j
k
l
l

In B1 enter:
=IF(COUNTIF(A:A,A1)=1,1,"")

In B2 enter:
=IF(COUNTIF(A:A,A2)=1,MAX(B$1:B1)+1,"") and copy down

We see:

a
a
b 1
c 2
d
d
e 3
f 4
g
g
g
h
h
i 5
j
j
j
k 6
l
l

Each of the required rows has been asigned a unique number. Finally in C1
enter:

=IF(MAX(B:B)<ROW(),"",OFFSET($A$1,MATCH(ROW(),B:B) ,0)) and copy down

We now see:

a c
a d
b 1 f
c 2 g
d j
d l
e 3
f 4
g
g
g
h
h
i 5
j
j
j
k 6
l
l

Only those items appearing once appear in column C
--
Gary''s Student - gsnu2007k


"Jonny Ross" wrote:

hiya,

i see lots of posts resolving the following:-

a
a
b
c
d
d
e

to

a
b
c
d
e

however what i want to do is take a column and turn

a
a
b
c
d
d
e

into

b
c
e

i.e showing me data that is present once only...

any ideas how i do this? excel 2007

many thanks