| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
very hard deleting rows issue
I have 4000 rows of data that looks like this:
Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
You can do that without disturbing your data using Filters
Filter Unique Records (Debra Dalgleish) http://www.contextures.com/xladvfilter01.html#FilterUR --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
Sub Test()
Dim iLastRow As Long Dim i As Long Dim delRange As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then If delRange Is Nothing Then Set delRange = Cells(i, "A") Else Set delRange = Union(delRange, Cells(i, "A")) End If End If Next i If Not delRange Is Nothing Then delRange.EntireRow.Delete Set delRange = Nothing End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
Bob
This works part of the way. It deletes to much. I don't want it to delete ANY lines for a number that has more than one status. I only need it to delete extra rows when the number and the status match in every case. So for a number that has 3 Adds and nothing else, delete the two extras. For a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY rows. There is an extra column of data related to these two items I need to preserve but don't want to include because it creates a unique entry for EVERY row. I've tried filtering and it also doesn't work correctly either. Thanks so much! Lynn "Bob Phillips" wrote: Sub Test() Dim iLastRow As Long Dim i As Long Dim delRange As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then If delRange Is Nothing Then Set delRange = Cells(i, "A") Else Set delRange = Union(delRange, Cells(i, "A")) End If End If Next i If Not delRange Is Nothing Then delRange.EntireRow.Delete Set delRange = Nothing End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
Your results look wrong to me.
Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added <<<<<<<<<<<<<<<< c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added <<<<<<<<<<<<<<<< as far as I can see the two chevron marked items are duplicates, so my code deletes them. Tell me in what way they are unique because I can't see it. -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... Bob This works part of the way. It deletes to much. I don't want it to delete ANY lines for a number that has more than one status. I only need it to delete extra rows when the number and the status match in every case. So for a number that has 3 Adds and nothing else, delete the two extras. For a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY rows. There is an extra column of data related to these two items I need to preserve but don't want to include because it creates a unique entry for EVERY row. I've tried filtering and it also doesn't work correctly either. Thanks so much! Lynn "Bob Phillips" wrote: Sub Test() Dim iLastRow As Long Dim i As Long Dim delRange As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then If delRange Is Nothing Then Set delRange = Cells(i, "A") Else Set delRange = Union(delRange, Cells(i, "A")) End If End If Next i If Not delRange Is Nothing Then delRange.EntireRow.Delete Set delRange = Nothing End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
Yes it works perfectly to delete any extras of the same number and status.
However, if a number shows more than one status I need to keep them all, I don't want to delete any of them. I only want to delete extras if the status is the same for each instances of the number. This is the scope of why I need this in this manner. I have a number with many options. If that number's options is all Removed, then I can make a blanket statement that that number's status is Removed. However, a number could have two Adds, one Remove and one Not Changed. I need to know the options associate with all of those. The uniqueness I spoke of involves another column of data that would make EVERY entry unique and that's why I kept it out. I don't need to use it except AFTER these deletes are performed. I'm sorry if I'm not explaining this appropriately. I'm very new to Excel functions and macros. I never used it like this before and if the 3rd column is useful in performing this, I'm so sorry for not including it and wasting your time.... Here's what I have in total: number status option 123 Removed 1 123 Removed 2 123 Removed 3 987 Added 1 987 Added 2 987 Removed 3 987 Not Change 4 I would need to keep all the 987 but only one (and I don't care which one) of 123. "Bob Phillips" wrote: Your results look wrong to me. Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added <<<<<<<<<<<<<<<< c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added <<<<<<<<<<<<<<<< as far as I can see the two chevron marked items are duplicates, so my code deletes them. Tell me in what way they are unique because I can't see it. -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... Bob This works part of the way. It deletes to much. I don't want it to delete ANY lines for a number that has more than one status. I only need it to delete extra rows when the number and the status match in every case. So for a number that has 3 Adds and nothing else, delete the two extras. For a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY rows. There is an extra column of data related to these two items I need to preserve but don't want to include because it creates a unique entry for EVERY row. I've tried filtering and it also doesn't work correctly either. Thanks so much! Lynn "Bob Phillips" wrote: Sub Test() Dim iLastRow As Long Dim i As Long Dim delRange As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then If delRange Is Nothing Then Set delRange = Cells(i, "A") Else Set delRange = Union(delRange, Cells(i, "A")) End If End If Next i If Not delRange Is Nothing Then delRange.EntireRow.Delete Set delRange = Nothing End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
very hard deleting rows issue
If you are holding back information, how can we possibly provide accurate
answers. The code cannot possibly take account of a column it knows nothing about. The code works perfectly within the limitations of the information that you have provided. -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... Yes it works perfectly to delete any extras of the same number and status. However, if a number shows more than one status I need to keep them all, I don't want to delete any of them. I only want to delete extras if the status is the same for each instances of the number. This is the scope of why I need this in this manner. I have a number with many options. If that number's options is all Removed, then I can make a blanket statement that that number's status is Removed. However, a number could have two Adds, one Remove and one Not Changed. I need to know the options associate with all of those. The uniqueness I spoke of involves another column of data that would make EVERY entry unique and that's why I kept it out. I don't need to use it except AFTER these deletes are performed. I'm sorry if I'm not explaining this appropriately. I'm very new to Excel functions and macros. I never used it like this before and if the 3rd column is useful in performing this, I'm so sorry for not including it and wasting your time.... Here's what I have in total: number status option 123 Removed 1 123 Removed 2 123 Removed 3 987 Added 1 987 Added 2 987 Removed 3 987 Not Change 4 I would need to keep all the 987 but only one (and I don't care which one) of 123. "Bob Phillips" wrote: Your results look wrong to me. Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added <<<<<<<<<<<<<<<< c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added <<<<<<<<<<<<<<<< as far as I can see the two chevron marked items are duplicates, so my code deletes them. Tell me in what way they are unique because I can't see it. -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... Bob This works part of the way. It deletes to much. I don't want it to delete ANY lines for a number that has more than one status. I only need it to delete extra rows when the number and the status match in every case. So for a number that has 3 Adds and nothing else, delete the two extras. For a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY rows. There is an extra column of data related to these two items I need to preserve but don't want to include because it creates a unique entry for EVERY row. I've tried filtering and it also doesn't work correctly either. Thanks so much! Lynn "Bob Phillips" wrote: Sub Test() Dim iLastRow As Long Dim i As Long Dim delRange As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then If delRange Is Nothing Then Set delRange = Cells(i, "A") Else Set delRange = Union(delRange, Cells(i, "A")) End If End If Next i If Not delRange Is Nothing Then delRange.EntireRow.Delete Set delRange = Nothing End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Lynn Bales" wrote in message ... I have 4000 rows of data that looks like this: Number Status C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added What I need to accomplish is to eliminate all but one row where the number and status are the same but keep ALL the rows where the number has several different statuses. So basically I need the above to look like: Number Status C02504 Removed c02536 Removed c02536 Added c02536 Added c02536 Not Changed C02564 Not Changed C03869 Removed C03869 Added C03869 Added |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
| Deleting first character issue | New Users to Excel | |||
| printing issue - want to repeat two rows and also 5 rows in column | Excel Discussion (Misc queries) | |||
| Excel hard code those #'s without deleting the formula or typing# | Excel Discussion (Misc queries) | |||
Hybrid Mode
