Filtering for the correct duplicate

[yuenk]

I’m working with an extract that has “duplicates” that need to be filtered. I can’t apply filters as the extract has hundreds/thousands lines of data that filtering can’t do. Each Work Number has an area that is assigned a number which then has a corresponding unique Serial Number. I need the Work Number Version that has the highest Version status and to have the other Versions deleted.

The focus is on the serial numbers for identifying duplicates. When a duplicate is found it would look at the Version Status column and delete the lower versions. The order of Version statuses is Initiate --> Working --> Approved --> Official.

I have had help with this and it worked with the example I provided but now that I applied my work to the sample (attached file) in the same format the macro doesn't work. In the attachment Work Number 1234567 has 4 Versions, version 4 being the highest. I'd then like to have just versions 1,2, 3 deleted and the rest of the worksheet left alone.
Test Run.xlsm