Solved

Eliminating Dupes with Pivot Table and VLookup

Posted on 2012-03-14
7
354 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 42

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 42

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 42

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 42

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 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