?
Solved

Isolating Duplicate Entries in Excel

Posted on 2013-11-17
8
Medium Priority
?
379 Views
Last Modified: 2013-11-20
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!
0
Comment
Question by:RobertCrist
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39654285
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
 
LVL 6

Expert Comment

by:Michael
ID: 39654399
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
 
LVL 1

Author Comment

by:RobertCrist
ID: 39654401
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:Michael
ID: 39654459
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
 
LVL 1

Author Comment

by:RobertCrist
ID: 39654946
Example-Thingy.xlsx

Here is a bigger sample of the data to work with that you requested.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39655813
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
 
LVL 1

Author Comment

by:RobertCrist
ID: 39655846
Thank you, Jazzy. I apologize but I don't see your attachment. Perhaps I'm missing a step?
0
 
LVL 6

Accepted Solution

by:
Michael earned 2000 total points
ID: 39656037
My mistake; I missed a step.

Joop
Example-Thingy.xlsm
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question