Help with duplicates

[demasijr6]

Hello,

I need assistance with duplicates in Excel. I work with spreadsheets and tables. Currently I have a table that I know contains duplicates which is driving inaccurate numbers. I know how to find duplicates using conditional formatting BUT that doesn't solve my problem. I need to find how many instances of the identification number there are and remove the second or greater duplicate. In other words if identification number 22334455 has 3 instances in my table I want to keep the first and remove the second and third.

Would I still use conditional formatting to identify the duplicates? I thought I could use countifs, but I must be doing something wrong because it's not providing me the results I'm looking for.

I would really appreciate any help as soon as possible. I hope I'm asking my question correctly. Please let me know if any clarification is needed.

 

[dreid1011]

With Excel 365, you can use the UNIQUE function to return a list of unique results. UNIQUE function - Microsoft Support

 

[awoohaw]

Although some work, you can create a helper column with a formula. Assuming your ID's start at Cell A2.
The helper column formula would be
Label the helper column "Has >2 Duplicates"

=COUNTIF($A$2:A2,A2)>2

and copy down.

Then filter the entire data for that column being TRUE.
Then delete all those rows.

There is probably a way to do it systematically and repeatedly with Power Query, as well.

 

[demasijr6]

Although some work, you can create a helper column with a formula. Assuming your ID's start at Cell A2.
The helper column formula would be
Label the helper column "Has >2 Duplicates"

=COUNTIF($A$2:A2,A2)>2

and copy down.

Then filter the entire data for that column being TRUE.
Then delete all those rows.

There is probably a way to do it systematically and repeatedly with Power Query, as well.

Thank you. I think this is what I needed. It seems to have worked. I will test it over the next few days. I really appreciate your time!

 

[awoohaw]

happy to help.

Best wishes!

 

[demasijr6]

happy to help.

Best wishes!

Thank you again, I have discovered I need the next part of my process. Now that I have identified all of the duplicates I need to keep the first instance and delete the rest. Is there a formula I can use for that?

 

[demasijr6]

I figured it out. I changed the formula by saying >1 instead of >2. It marked all of the second instances with true and when I filtered I was able to delete them. Thank you very much @awoohaw

 

[awoohaw]

I figured it out. I changed the formula by saying >1 instead of >2. It marked all of the second instances with true and when I filtered I was able to delete them. Thank you very much @awoohaw

You're welcome. Your original question was for greater than two, so that is why I had that.

Best Wishes!