How to blank out cells with duplicate values?

[Bleu_808]

Okay - after an hour on trying to work on what I thought was an easy
task 0 I must ask for help!

My column A is a list of order numbers, each order is separated by a
blank row...

ex:
1234
1234
1234
1234

4321
4321
4321

6789
6789

What I would like to do is delete the duplicate order numbers, without
affecting the rest of the row. I thought this might be a simple if
statement, but no. Any ideas?

Thanks in advance - Bleu

 

[Gary''s Student]

Try:
1. select the column
2. pull-down:
Data > Filter : Advanced Filter > and check unique records only

This should eliminate the duplicates

 

[Bob Phillips]

Some VBA

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Application.CountIf(Columns(1), Cells(i, "A")) > 1 Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bleu_808]

Thanks, but this affects the rest of the rows. I need to be able to
view the info in the row, just don't want to see this cell duplicated.
Unless I am doing it wrong???

 

[Bleu_808]

Bob, Thanks - but this did not work for me. Let me explain a little
better...

ex:
1234 Part #1
1234 Part #2
1234 Part #2
1234 Part #4


4321 Part #1
4321 Part #2
4321 Part #3


6789 Part #1
6789 Part #2

I still want to view the informationon the rest of the row that has
the duplicate number, I just want to clear only the cell of the
duplicate.

Sorry if I wasn't clear

Bleu

 

[Dominic]

Bleu,

You could do this using a helper column I believe.

Insert a column next to your order number column. Then beginning with the
second row type the formula =IF(A2="","",IF(A2=A1,"",A2)). Copy this formula
down the column.
Where column A is the column your order numbers are in.

Then copy the column, the paste:special:values over the top.

Is that what you are looking for?

 

[Bryan Hessey]

Select cell A1, then select the A column (check that A1 is the activ
cell, the odd highlight)
Format, Conditional Format, Formula is
=COUNTIF(A$1:A1,A1)<>1
and set font colour to White (on white)

duplicates are then hidden

Hope this helps

--

 

[Bob Phillips]

Perhaps you mean this

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Application.CountIf(Columns(1), Cells(i, "A")) > 1 Then
Cells(i, "A").Clearcontents
End If
Next i

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bleu_808]

Bob,

This is exactly what I was looking for! Thank you!

Thank you everyone for your help too! It's much appreciated!

Bleu