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

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

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

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

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