Finding Multiple Entries & Deleting All but One

[Brandy]

I have a large and lengthy spreadsheet. I have one column representing PO
#'s. The spreadsheet is already sorted by PO #s and they are in order. For
instance though, I have 6 lines that are for PO # 200205. I would like to see
#200205 only on the first line and the next 5 lines not have a PO # listed at
all. So what kind of command can I type so that it will only show the first
PO # and delete the rest that match that #? Does that make sense?

 

[Don Guillett]

Several ways such as
data>filter>advanced filter>copy
or a loop from the bottom that says if the cell above is the same delete the
row.

 

[TRYING]

DON: Seems to me Brandy does not want to delete the rows. She only wants to
clear the PO number from the second and subsequent cells. The other 5 rows
for PO # 200205 might contain information different from the first one.

BRANDY: Unless you have a particularly good reason for wanting to remove the
second and subsequent appearance of the same PO #, you might be better off
keeping them.

 

[Brandy]

Yeah, I don't want to delete the rows. I just want to clear the cell for the
second and subsequent cells that match that #.

 

[Don Guillett]

I also do NOT suggest you do this but this will do it.

Sub clearcells()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then Cells(i, mc).ClearContents
Next
End Sub

 

[Jim Thomlinson]

I would use conditional formatting for something like that. If you delete the
values then they are tough to get back if you decide that you need them later
on...

In cell A2 (assuming your PO's are in column A add a conditional format
similar to this...
Cell Value Equals A1
Format the text to blend into the background (usually white). Copy that
formatting to the entire column...

The numbers will still be there but they will not appear visible.

 

[Brandy]

And if I try this on a sample copy, where would I put this in my worksheet?
And should my PO #s be in column A ?

 

[Don Guillett]

If not col A, change mc= to whatever
Put in a macro module and fire it. Don't save your workbook until satisfied.
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm