Delete the oldest repeat

[MagicMan]

Hi Guys,

I have to monitor the training completion rates. Unfortunately, the reporting extract is less than ideal, with repeats etc.

The participants are meant to put their employee number in, but that doesn't always happen.

I'm looking for a way to manage this dynamic list (the number of records is likely to change)
I want to:

1. Delete anything in Column I that isn't "Passed"
2. Look for duplicates in Column G and for each duplicate, delete the oldest based on the value in column B
   • Delete the entire row of the oldest duplicate
   • Unfortunately, not all employee numbers are completed. I'm happy for a control column concatenating Column E and Column F instead

I've attached a sanitised workbook with two tabs - "Base Data" and "Intended Outomce"

• "Base Data" is indicative of how I would receive the data
• "Intended Outcome" is how I want it to look after the manipulation is complete

I would also be grateful if you could comment the life out of any VBA so I can read and, hopefully, learn.

Thanks all

MagicMan

[WideBoyDixon]

Assuming that the records are already ordered by date (i.e. oldest to newest), this might work for you:

Please Login or Register  to view this content.

Suggest you run it on a copy of your base data!

WBD

[Richard Buttrey]

Here's another option. It assumes the data is already sorted by column B descending. If not just add another instruction to the macro to sort it first.

In general I prefer to avoid loops since they can be very time consuming and it's the most efficient way I know to do this sort of stuff.

This approach :

A. Identifies the number of Rows
B. Creates helper column J which concatenates the last/First name
C. Creates a formula in column K which identifies a duplicate name or <> "Passed"
D. Filters column K for the word 'delete'
E. Stops Excel halting to alert you about deleting
F. Deletes all the filtered rows
G. Removes the filter
H. Deletes the helper columns J & K

Please Login or Register  to view this content.

[MagicMan]

WBD and Richard,

Thank you very much for your input. I've added to both of your reps.

I used WBD's because it was first in (I'm not unduly concerned about length of time to execute), but it would be interesting to test our Richard's solution to see what it does in terms of time difference to operate.

I'm planning on assigning the macro to a button on a front sheet. Do I simply need to activate the "Base Data" sheet as the first step (i.e. simply put Sheets("Base Data").Activate at the beginning of the macro.

Please Login or Register  to view this content.

[WideBoyDixon]

You could do that or you could use this modified version:

Please Login or Register  to view this content.

WBD