A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding Multiple Entries & Deleting All but One



 
 
Thread Tools Display Modes
  #1  
Old January 9th, 2008, 11:26 PM posted to microsoft.public.excel.misc
Brandy
external usenet poster
 
Posts: 113
Default Finding Multiple Entries & Deleting All but One

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?
  #2  
Old January 9th, 2008, 11:30 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default Finding Multiple Entries & Deleting All but One

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?


  #3  
Old January 9th, 2008, 11:48 PM posted to microsoft.public.excel.misc
Trying
external usenet poster
 
Posts: 49
Default Finding Multiple Entries & Deleting All but One

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?



  #4  
Old January 9th, 2008, 11:55 PM posted to microsoft.public.excel.misc
Brandy
external usenet poster
 
Posts: 113
Default Finding Multiple Entries & Deleting All but One

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?



  #5  
Old January 10th, 2008, 12:14 AM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default Finding Multiple Entries & Deleting All but One

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?



  #6  
Old January 10th, 2008, 01:24 AM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default Finding Multiple Entries & Deleting All but One

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?

  #7  
Old January 10th, 2008, 02:40 PM posted to microsoft.public.excel.misc
Brandy
external usenet poster
 
Posts: 113
Default Finding Multiple Entries & Deleting All but One

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?




  #8  
Old January 10th, 2008, 03:27 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default Finding Multiple Entries & Deleting All but One


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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.