VBA: Delete Duplicate Values on Condition

EdCarryhands

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Capture.PNG
Capture2.PNG
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,381
Messages
5,853,490
Members
431,593
Latest member
AAAaahhh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top