Isolating Duplicate Entries in Excel

I've been trying to figure out how to go about completing what I perceive to be a rather complex cell operation involving finding a duplicate entry and then performing an operation on said duplicate entries within the sheet.

Screenshot of an example section of the spreadsheet to illustrate my intentions.
From the screen shot listed above, what I'm looking to try to accomplish is finding the duplicate 'blue' entry of the 'verified' 'white' entry. In other words, 623.225.9999 is a verified phone number. I want to set up a script of some kind to find it's accompanying entry in blue (in this case, right above it, but it won't always be that easy), mark that number as 'verified' (i.e, the J column will say 'yes' like it does in the white cell), and then delete the white row altogether.

I've tried using conditional formatting but that doesn't automate the process. I've also tried advanced filters thinking I could take the results of a unique filter and dump it into a second sheet and continue doing operations there, but the end result is I can't copy those results into a separate sheet, nor does that help me automate the process.

I'm sure there are easier ways to do this, perhaps in Access, but if I can I'd like to keep it in Excel. Of course I'm not completely rigid in this regard.

I appreciate any and all feedback!
LVL 1
RobertCristAsked:
Who is Participating?
 
MichaelConnect With a Mentor Business AnalystCommented:
My mistake; I missed a step.

Joop
Example-Thingy.xlsm
0
 
QuinnDexCommented:
not the most straight forward task,  you need to use excels filter function. this page explains it well


http://www.techrepublic.com/blog/microsoft-office/how-to-find-duplicates-in-excel/
0
 
MichaelBusiness AnalystCommented:
Hello RobertCrist,

this can be done by a vba macro.

Is it allowed to sort the dataset? This would speed up the process significantly, since it won't have to search for duplicate entries through the entire dataset, but just below or above.

Is there a maximum of one duplicate entry or could there be more?

Joop
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
RobertCristAuthor Commented:
There could be multiple entries. A single group could be 3 rows, it could be 20 rows. I can't determine how best to group those rows dynamically either.
0
 
MichaelBusiness AnalystCommented:
Can you post a file with a sample of the data, which a bit larger than the screenshot in your original post? Then I will try to write you a macro for this task.

Joop
0
 
RobertCristAuthor Commented:
Example-Thingy.xlsx

Here is a bigger sample of the data to work with that you requested.
0
 
MichaelBusiness AnalystCommented:
Please try the macro in the attached file to see if this fits your needs.

Remember to try it on a copy of your workbook to prevent the loss of data.

Joop
0
 
RobertCristAuthor Commented:
Thank you, Jazzy. I apologize but I don't see your attachment. Perhaps I'm missing a step?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.