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

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"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]

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

--

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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