Solved

Eliminating Dupes with Pivot Table and VLookup

Posted on 2012-03-14
7
350 Views
Last Modified: 2012-08-14
Duplicate-Datasets.xlsxHello,

I've been asked to find duplicates in 2 data sets and consolidate them using both a Pivot Table and VLookup but I'm not really sure how to do this.  I've attached an Excel 2007 file with 2 very simple lists of 20 items each with some duplicates.

Thanks!
0
Comment
Question by:monbois
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37720935
Alt-D-P will get you the pivot table popup to allow for multiple consolidation ranges.


Is there a reason you don't just use DataRibbon->Remove Duplicates?  Or, are you having to identify the duplicates?


Are you trying to consolidate the duplicates, or the two tables?

Here's a link on multiple consolidation ranges with pivot tables:
http://www.contextures.com/xlPivot08.html

Dave
0
 

Author Comment

by:monbois
ID: 37720972
Hi Dave,

This was a question posed to me by a recruiter.  Most of my experience is with Access.  I'm familiar with Excel but I've never come across this before, and chances are the recruiter herself doesn't know anything about Excel.  I've discovered some great stuff since I posed the question, such as:

Data-> Sort & Filter -> Advanced Filter -> Copy to Another Location -> Unique Records Only

I can use VLookup to identify duplicate values and a Pivot Table to list unique values in a list.

What 'DataRibbon' tab has the 'Remove Duplicates' command? I cant't find that great feature, but it certainly sounds useful, to say the least.

Thanks.
0
 

Author Comment

by:monbois
ID: 37720988
BTW, Dave...

The goal is to consolidate data and eliminate duplicates (potential client just had huge merger).

Also, thanks for the link.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37720990
If you're using Excel 2007+ (and from your post, it appears so), then you have many ribbon tabs - e.g., HOME, INSERT, etc.,

Go to the DATA tab, then look toward the middle and you should see DATA TOOLS (group name at the bottom of each group) and inside that group, you should see REMOVE DUPLICATES.

Dave
0
 

Author Closing Comment

by:monbois
ID: 37721007
Wow! Great one-step solution! Thanks!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37721044
1. hit Alt-D-P to pull up the Pivot Table dialog supporting multiple consolidation ranges.
2. select Multiple consolidation ranges from the Step 1 dialog (NEXT)
3. Select "I will create the page fields" in Step 2a (NEXT)
4. Select the first Range (DataSet 01), then hit Add in Step 2b.  Then select the second Range (DataSet 02), and hit Add.
5. For this example, create 1 page field, then click on your first dataset range, and name the page field "DataSet01", and do the same for the second, naming "Dataset02".
6. Finish creation of the pivot table

Now, to organize for duplicates:

1. put Page1 in the Report filter
2. put Value and Column in the Row Labels
3. put Row in the Values area, and change the aggregation to COUNT.

Attached is the workbook that does this, and you can filter (as I have) to sort descending by Count using the downarrow on Row Labels, then More Sort Options, Descending by Count of Row.

Now, you have a list of duplicates first.

The other way to find duplicates from one set to the other is using VLOOKUP (or you could use MATCH or COUNTIF).

See attached, as I've added these formulas Dataset 01.  Now, you try to find duplicates using these formulas from Dataset02.

Dave
Duplicate-Datasets.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37721049
You might need to be able to do this the "Old Fashioned" way (that feature doesn't exist in Excel 2003), and sometimes the recruiter won't want you to eliminate them, but to identify them.  The post, above, helps with that.

Cheers,

Dave
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now