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?

Several ways such as
datafilteradvanced filtercopy
or a loop from the bottom that says if the cell above is the same delete the
row.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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: 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.

"Don Guillett" wrote:

Several ways such as
datafilteradvanced filtercopy
or a loop from the bottom that says if the cell above is the same delete the
row.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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?

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

"TRYING" wrote:

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.

"Don Guillett" wrote:

Several ways such as
datafilteradvanced filtercopy
or a loop from the bottom that says if the cell above is the same delete the
row.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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 #.

"TRYING" wrote:

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.

"Don Guillett" wrote:

Several ways such as
datafilteradvanced filtercopy
or a loop from the bottom that says if the cell above is the same
delete the
row.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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?

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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 #.

"TRYING" wrote:

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.

"Don Guillett" wrote:

Several ways such as
datafilteradvanced filtercopy
or a loop from the bottom that says if the cell above is the same
delete the
row.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brandy" wrote in message
...
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?

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

Jim Thomlinson


"Brandy" wrote:

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?