VBA: Delete Duplicate Values on Condition

[EdCarryhands]

Hi All,

Please help me with this.

I have a sheet with Column A(ID e.g. 1111-2222-3333-4444) and Column B(Status e.g. Installed, Inactive, Cancelled).

It has several duplicates on both columns. My goal is to have only one ID and if there is Installed status amongst the duplicates it has to be it. It is possible that 2 duplicates both Inactive then 1 has to be deleted. Also some IDs are duplicated 3,4, even 5 times.

Do you guys have any idea?

Thanks in advance.

 

[JLGWhiz]

How do you know which status is the latest? You could have the same ID with all three statuses, so if you just pick 'Installed' to keep, it might not be the correct status. Also, what would be the choice between Inactive and Cancelled? I think a clearer explanation of the criteria to delete duplicate ID entries needs to be made.

 

[EdCarryhands]

How do you know which status is the latest? You could have the same ID with all three statuses, so if you just pick 'Installed' to keep, it might not be the correct status. Also, what would be the choice between Inactive and Cancelled? I think a clearer explanation of the criteria to delete duplicate ID entries needs to be made.

There is no latest status they are not time stamped. It is an extract from the current status of our service. The source system will give you an ID and a Status (Inactive and Cancelled is the same with different words). In a perfect word one ID would be listed once either Active or Inactive( or cancelled), but if any tinkering was done with the service the ID can give you one Inactive and one Active which means the service IS active so I need to manually delete the inactive ID. Does this make sense?

 

[EdCarryhands]