PDA

View Full Version : Delete Partially Duplicate Rows based on Multiple Values



Mattheus
08-28-2019, 08:53 AM
In my organization, I receive reports of employee training. These reports show every employee who has been enrolled in the training and their training completion status. Problems arise when people get enrolled in our training more than once. If that happens, the employee shows up multiple times and with different statuses. These statuses are either Dropped, Enrolled, Complete, or In-Progress. I have attached an example of the report.

I am trying to get Excel VBA to automatically clean up this data so I end up with a list that only shows each employee once. If they have completed the training, I don’t need to see that they have also been dropped from the training.

Is it possible to create a macro that will look at each employee and their status, and then make a decision to delete all the duplicates of an employee if they have already completed the training? I’ve been able to make it delete all of the instances where the employee has multiples of the same status with this code:


ActiveSheet.Range("$A$1:$I$950").RemoveDuplicates Columns:=Array(2, 8), Header:=xlYes


That still leaves me with duplicates if they have more than one status. Haven’t been able to find any posts about this topic close enough to work for me.

In short, I am trying to create a VBA macro in Excel that will look at any duplicates of rows based on certain criteria. If there is a duplicate of an individual, I want the code to look at the different statuses of their training completion and make a decision on which to delete.

Thanks in advance!

Leith Ross
08-28-2019, 12:25 PM
Hello Mattheus,

Welcome to the forum!

The rules governing the status and what to do when a duplicate is found need to be explained more clearly. Can you provide us with a few samples to illustrate what needs to be done?

Mattheus
08-28-2019, 01:17 PM
Thanks for your response,

Our training system works as such-
Employee is removed from the training- status is ‘Dropped’
Employee is added to the training- status is ‘Enrolled’
Employee begins taking the training- status is ‘In-Progress’
Employee completes the training- status is ‘Completed’

When there is a duplicate, it’s usually because an employee was added to the training after they have already completed it. When that happens, they show up multiple times on the record.

I want Excel to look at this duplicate, identified by the Empl ID column, and see that they are marked Completed in column H. Because they are marked completed, I then wish for Excel to delete all additional times the employee shows up on the list, regardless of their status in any other entries.

I have updated the attached Excel sheet to demonstrate the process. The sheet labeled ‘Original’ is how the sheet appears when I receive it, and includes many duplicates. The sheet labeled ‘In-Progress’ shows the duplicates I wish for Excel to identify, as the three employees on the list have an entry in column H which shows they have completed the training. The sheet labeled “End Product’ shows the sheet after all the duplicates have been deleted, leaving only the entries which show the employee has completed the training.

Leith Ross
08-28-2019, 04:30 PM
Hello Mattheus,Thank you taking the time to expand the original workbook with with before and after examples. This is just what I needed to write the macro.

Mattheus
08-30-2019, 04:38 AM
Thanks!
I shall eagerly await your solution.