How to delete selected duplicated entries based on colour and or value

[TedX]

Hi, I have a long list of names that gets added to every day and I've formatted it so that every time a duplicate appears, the cell background colour goes

light green, sometimes they are 2 or 3 cells clumped together, and other times just one by itself. I delete the row, by selecting it and right mouse clicking to show a menu and then selecting delete. This works fine except for the following: The original entry higher in the long list, has a set of numeric values, I want to keep, whilst the lower down the list (new entry) duplicate has no value, that the one I want to delete. So I can't just use a filter to select one colour and delete because I have no control over which duplicate will be deleted, probably the highest duplicate on the list which is the exact opposite of what I want to do.

So I have no problem with selecting the duplicates down the bottom of the list and highlighting them (selecting them), except, I can only delete non-stop runs of rows to delete. In the image, I can only delete 9022 by itself, I can delete 9025 to 9027 inclusive in a single action, but I cannot select 9022 and 9025, 9026 and 9027 all at the one time and delete them. At least I can't do it via Excel directly with a mouse, I assume there is a VBA solution but I can't seem to work it out. All help would be appreciated, I suspect this is a few simple lines of VBA code using the colour 198 239 206. Thank you.

 

[TedX]

Not sick. Because I cannot test your color scheme, I suggest you upload a sample spreadsheet to a third party location such as Box.Net or Dropbox.Com so that I can test my code on your actual data. Only 8-12 records in the worksheet.

I can create a dropbox account or for instant delivery, I can send it to you via e-mail, would you prefer that?

 

[alansidman]

Post the link to Drop Box here. It will let you share specific files and keep all other files private.

 

[TedX]

Post the link to Drop Box here. It will let you share specific files and keep all other files private.

I created the DropBox and hope I did it all correctly. Book1.xlsm

 

[alansidman]

The issue that you are having is the cells in Green are conditionally formatted. That means that the cells are not recognized as having color. So to delete those you must use the same criteria that you used to color them conditionally. The easiest way to delete those is to use Power Query. Load your file to the PQ editor which is located on the Data tab and called Get and Transform Data. Here is a short tutorial. Scroll down to the video.

Excel Power Query Remove Duplicates | MyExcelOnline

You can easily remove duplicates from your data using Power Query. Click here to learn about Excel Power Query Remove Duplicates

www.myexcelonline.com

 

[TedX]

The issue that you are having is the cells in Green are conditionally formatted. That means that the cells are not recognized as having color. So to delete those you must use the same criteria that you used to color them conditionally. The easiest way to delete those is to use Power Query. Load your file to the PQ editor which is located on the Data tab and called Get and Transform Data. Here is a short tutorial. Scroll down to the video.

Excel Power Query Remove Duplicates | MyExcelOnline

You can easily remove duplicates from your data using Power Query. Click here to learn about Excel Power Query Remove Duplicates

www.myexcelonline.com

Thank you, I will but right now it's close to 4.00 AM, so I must go to sleep, but sometime tomorrow, I will let you know how it goes. Thank you sincerely, you have been more than generous with your time and experience.

 

[TedX]

Thank you, I will but right now it's close to 4.00 AM, so I must go to sleep, but sometime tomorrow, I will let you know how it goes. Thank you sincerely, you have been more than generous with your time and experience.

Hello Alansidman, I just wanted to let you know that the Power Query did not really suit my requirements, it does of course remove duplicates, but can't work out which of the duplicates I want to delete. I assume it has a top-down approach working from Row 2, given the header is in row 1. I actually need the duplicates to be removed from the bottom up. VBA does this sort of navigation very well, but because the duplicate doesn't have a colour, I can't use VBA. Unless of course, there is a way to do an If statement where every single text string (Horses Name) is compared going upwards and if one is found, then the lowest row numerically (of the matched strings) is deleted. That sort of coding is way above my current skill set, I'm afraid. So I sincerely want to thank you for all the time and energy you pumped into me, you have been remarkable. I hope we catch up again some other time on an easier assignment.

 

[alansidman]

Thanks for the feedback. Good Luck.