|
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? |
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? |
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 01:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com