Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Eliminating Dupes with Pivot Table and VLookup

Posted on 2012-03-14
7
Medium Priority
?
360 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
[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
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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