Solved

Eliminating Dupes with Pivot Table and VLookup

Posted on 2012-03-14
7
352 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

930 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

9 Experts available now in Live!

Get 1:1 Help Now