Eliminating SUBSEQUENT duplicate contents

[Gary]

A1 = 'Smith'
A2
A3
A4 = 'Smith'
A5
A6 = 'Jones'
A7
A8
A9
A10
A11 = 'Jones'
A12
A13
A14 = 'Peters'
A15
A16 = 'Peters'

How can I keep the FIRST occurrence of each label (in A1, A6 and A14) and
eliminate the SUBSEQUENT occurrences (in A4, A11 and A16)?

 

[Frank Kabel]

Hi
try the following formula in B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))
and copy down

 

[Ken Wright]

Do you actually want to keep them in the cells they are currently in, or are you
simply looking for a list of Unique names, in which case take a look at Data /
Filter / Advanced Filter, 'Copy to another location & Unique records only'

 

[gary]

I need to keep the contents of every cell except for those
in A4, A11 and A16. Here's the results I need:


======= CURRENT ======== === DESIRED RESULTS =====
cell contents cell contents

A1 = 'Smith' B1 = 'Smith'
A2 = ' DDDDDDDDD' B2 = ' DDDDDDDDD'
A3 = ' EIDOEOEOE' B3 = ' EIDOEOEOE'
A4 = 'Smith' B4 = empty
A5 = ' ZZZZZZZZZ' B5 = ' ZZZZZZZZZ'
A6 = 'Jones' B6 = 'Jones'
A7 = ' RRRRRRRRR' B7 = ' RRRRRRRRR'
A8 = ' DDDDDDDDD' B8 = ' DDDDDDDDD'
A9 = ' RRRRRRRRR' B9 = ' RRRRRRRRR'
A10 = ' NNNNNNNNN' B10 = ' NNNNNNNNN'
A11 = 'Jones' B11 = empty
A12 = ' DDDDDDDDD' B12 = ' DDDDDDDDD'
A13 = ' QOTOYORO0' B13 = ' QOTOYORO0'
A14 = 'Peters' B14 = 'Peters'
A15 = ' WICIRI9TN' B15 = ' WICIRI9TN'
A16 = 'Peters' B16 = empty




Thanks,

Gary

 

[Frank Kabel]

Hi Gary
have you tried my formula. Should give you the desired result. If not
please come back with a description what you would like to change. Put
this in B1
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))
copy down

 

[gary]

Frank,

I'm not getting the desired results with your formula.
Here's what I'm getting:

Results of your formula Desired results

Smith Smith
DDDDDDDDD DDDDDDDDD
EIDOEOEOE EIDOEOEOE

ZZZZZZZZZ ZZZZZZZZZ
Jones Jones
RRRRRRRRR RRRRRRRRR
DDDDDDDDD
RRRRRRRRR
NNNNNNNNN NNNNNNNNN

DDDDDDDDD
QOTOYORO0 QOTOYORO0
Peters Peters
WICIRI9TN WICIRI9TN




Gary

 

[Frank Kabel]

Hi
that's interesting. I tried your testdata with my formula and got your
desired result?. Some ideas
If you copy the formula in B1
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))
- the mixed absolute/relative reference is essential ($A$1:A1,A1)
- After copying the formula, what is the formula in B2. It should look
like
=IF(A2="","",IF(COUNTIF($A$1:A2,A2)>1,"",A2))


If this does not work you may mail me your sheet and I'll set-up the
initial formulas (frank[dot]kabel[at]freenet[dot]de).

 

[gary]

Frank,

I've sent my example directly to your e-mail address.

Gary

 

[daniel chen]

How about this
C1=IF(OR(A1=B1,B1="empty"),"",A1)

Hi Gary
have you tried my formula. Should give you the desired result. If not
please come back with a description what you would like to change. Put
this in B1
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))
copy down

 

[JMay]

Highlight the Range A1:A10 - Menu Data Validation, Custom,
informula box enter =COUNTIF($A$1:$A$10,A1)=1
HTH