Remove Duplicates Based on other column Specific Value

Hi there,
I am new to this forum. Could you please help me with my issue to delete duplicate records based on other column specific Text? I have searched in so many ways but I couldn’t find a solution for my problem.

I have a macro found in this forum which is very useful to remove duplicates except first instance of Duplicates from Column “C”.
I need a macro which keeps one instance and remove other duplicates based on Other Column text value. In my case I need to keep either Cricket or Football from column “E” as a 1st instance and rest of all items should be deleted from Duplicates list. If none of the Duplicates have Cricket or Football in Column “E” needs to keep any value as 1st instance and remove the other duplicates.
Please find the attached excel spreadsheet having my current Macro which removes duplicates except 1st instance based on Column C.
1st tab having sampl data and second tab having the result what I am looking for.
I have arranged duplicates in a Sorting Order in Column C to understand easily. My actual project doesn’t have values in a sorting order.
Thanks in advance

Originally Posted by mario_berry

Im a bit confused with what you are trying to get here. looking at your dataset there is no fixed result unless you add additional columns with what you want to keep.

looking at the code, this is correct. I would say the issue here is the order you have your data.
perhaps modify the macro to sort the data before removing duplicates.

looking what i would do, i would sort by sport type, then country, then remove duplicates.

Hi Mario, thank you for your reply. Is it possible for you to provide an updated macro to solve my issue. Iam not good at writing Macros as Iam basic learner. I want to do the sorting and removal of duplicates through macro only. Please help me. Much appreciated.

what sort do you want

how would you sort the table that is

Originally Posted by mario_berry

what sort do you want

how would you sort the table that is

I mean removing duplicates through macro only..
First instance should contains either cricket or football is my primary requirement. If duplicates doesn't contain cricket or football, remove duplicates except 1st instance.
Sorry for my bad english.

The below is the code to sort by sport type, then sort by country.

Please Login or Register  to view this content.

don't be sorry for bad English, id have said it was good.

ok, this is going to mix things up a bit.
firstly, i would want to highlight the values you want to keep. i would do this by adding a column with a formula.

=IF(OR(E2="Cricket",E2="Football"),"Keep","")

The above highlights the values you want to keep.

Secondly, i would modify the VBA i posted earlier to sort the additional column.

Please Login or Register  to view this content.

you will notice i have changed the second sort to descending, this is otherwise the ones you dont want to keep will be at the top.

Hi Mario, your code helps me to understand the logic. But I found one issue relating to my project with your code. Is it possible to remove duplicates without Sorting the data in Sport (E) and Country (C) Columns .
As I am getting an issue while Sorting values. For example from the Duplicates List for the Country POLAND the result value should be Football. But due to sorting issue its showing "Chess" instead of Football.

Thank You Jindon. Your code Exactly What I am looking for.
Thank you Mario Your code also doing the same but in different method. I like the methods you shown with a Formula and sorting features will help in my project work.
Appreciated both of you Jindon & Mario.
Thanks to Excelforum as this is my first post.