Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows but keeping the most recent.
I am trying to delete duplicate rows in my spreadsheet. Please don't
just send a reference to Cip Pearson's website. I have been there, found what I needed but I can't get it to work in my instance. Baby steps and simplistic terms please. The spreadsheet is a monthly upload that has 19 columns and has between 500 - 4000. Currently column B is the workorder column this is duplicated because over the month a workorder could be modified several times, thus the duplicates. Column S has the dates of the changes and that is where I would like to find the most recent and then delete the other two, three sometimes up to 10 records for each workorder. Guidance on the use of the following code would be greatly appreciated. I am new to "code" and would like to become better. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1 If Cells(RowNdx, "b").Value = Cells(RowNdx - 1, "b").Value Then If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If End If Next RowNdx End Sub Thank you, Lynn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows but keeping the most recent.
What is this code not doing for you? By all appearances it should do the
job *as long as the sheet is sorted in Workorder number*. The only question in my mind is the line: If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value In your case, if you have 2 records with the same Workorder AND same change date, you will simply delete one of them, rather arbitrarily. You might want to change <= to just < so if you have two workorders with the same change date you leave them both intact for manual review/evaluation/deletion. Maybe this is actually OK, it depends on your data and how ChangeDate gets recorded. -- George Nicholson Remove 'Junk' from return address. "Lynn A." wrote in message om... I am trying to delete duplicate rows in my spreadsheet. Please don't just send a reference to Cip Pearson's website. I have been there, found what I needed but I can't get it to work in my instance. Baby steps and simplistic terms please. The spreadsheet is a monthly upload that has 19 columns and has between 500 - 4000. Currently column B is the workorder column this is duplicated because over the month a workorder could be modified several times, thus the duplicates. Column S has the dates of the changes and that is where I would like to find the most recent and then delete the other two, three sometimes up to 10 records for each workorder. Guidance on the use of the following code would be greatly appreciated. I am new to "code" and would like to become better. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1 If Cells(RowNdx, "b").Value = Cells(RowNdx - 1, "b").Value Then If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If End If Next RowNdx End Sub Thank you, Lynn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows but keeping the most recent.
Hi, Lynn.
"Lynn A." wrote in message om... I am trying to delete duplicate rows in my spreadsheet. Please don't just send a reference to Cip Pearson's website. I have been there, found what I needed but I can't get it to work in my instance. Baby steps and simplistic terms please. When you say "I can't get it to work", can you tell us a bit more? Is it giving an error message? Or is it just running through and nothing's happening at all? Have you tried stepping though this to follow it and where it's going through the If statements, or if RowNdx is staying with you? The spreadsheet is a monthly upload that has 19 columns and has between 500 - 4000. Currently column B is the workorder column this is duplicated because over the month a workorder could be modified several times, thus the duplicates. Column S has the dates of the changes and that is where I would like to find the most recent and then delete the other two, three sometimes up to 10 records for each workorder. Is Column S formatted as Date, Number, Text, General? Guidance on the use of the following code would be greatly appreciated. I am new to "code" and would like to become better. Here's what I would do. Create a new spreadsheet with a few samples of your data - maybe 10-15 rows. Open the Visual Basic Editor window (VBE) with Alt+F11. Add in the Debug.Print lines sown below - these will cause the named values to "print" to the Immediate window (the Immediate window is brought up in the VBE by hitting Ctrl+G). Shrink the VBE until you can see the Immediate window and a few lines of code. Step through the code using F8, and watch the values in the Immediate window. See if the values shown are what you expect. Watch the yellow highlight through the If statements and see if it's doing what you expect. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1 Debug.Print RowNdx ' shows what row is being evaluated Debug.Print Cells(RowNdx, "b").Value ' the work order number Debug.Print Cells(RowNdx - 1, "b").Value ' the number above it If Cells(RowNdx, "b").Value = Cells(RowNdx - 1, "b").Value Then Debug.Print Cells(RowNdx, "s").Value ' date of the 1st WO number Debug.Print Cells(RowNdx - 1, "s").Value ' date of the 2nd number If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If End If Next RowNdx End Sub Thank you, Lynn Hope this helps. Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows but keeping the most recent.
Like Nicholson, I recommend the rows be sorted by work order number
first. Sorting them secondarily by date would be even better. If they are sorted by work order number, then the following should work. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("b1").End(xlDown).Row To 2 Step -1 Do While Cells(RowNdx, "b").Value = Cells(RowNdx - 1, "b").Value If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If RowNdx = RowNdx - 1 If RowNdx = 1 Then Exit Sub Loop Next RowNdx End Sub HTH, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows but keeping the most recent.
Okay, thanks to all your suggestions I've modified it to sort first,
that works but all it does is flicker the screen until I break it, even with only 20 rows. Nothing comes up with the debug.print in the immediates window. There is obviously a loop somewhere and I'm not experienced enough to see it. Any other ideas???? Thanks, Lynn "merjet" wrote in message ... Like Nicholson, I recommend the rows be sorted by work order number first. Sorting them secondarily by date would be even better. If they are sorted by work order number, then the following should work. Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("b1").End(xlDown).Row To 2 Step -1 Do While Cells(RowNdx, "b").Value = Cells(RowNdx - 1, "b").Value If Cells(RowNdx, "s").Value <= Cells(RowNdx - 1, "s").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If RowNdx = RowNdx - 1 If RowNdx = 1 Then Exit Sub Loop Next RowNdx End Sub HTH, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collating Duplicate Rows Keeping all values intact and updating Qu | Excel Discussion (Misc queries) | |||
remove duplicate rows but keeping all columns | Excel Worksheet Functions | |||
Keeping duplicate rows | Excel Worksheet Functions | |||
Deleting duplicate rows.....there's more | Excel Programming | |||
Deleting Duplicate Rows | Excel Programming |