How to blank out cells with duplicate values?
 

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

How to blank out cells with duplicate values?
 

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

This should eliminate the duplicates

--
Gary's Student


"Bleu_808" wrote:

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

How to blank out cells with duplicate values?
 

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" wrote in message
oups.com...
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

How to blank out cells with duplicate values?
 

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???

How to blank out cells with duplicate values?
 

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

How to blank out cells with duplicate values?
 

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?

"Bleu_808" wrote:

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

How to blank out cells with duplicate values?
 

Select cell A1, then select the A column (check that A1 is the active
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

--


Dominic Wrote:
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?

"Bleu_808" wrote:

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




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537895

How to blank out cells with duplicate values?
 

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" wrote in message
oups.com...
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

How to blank out cells with duplicate values?
 

Bob,

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

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

Bleu