Eliminating Dupes with Pivot Table and VLookup

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!
Frank SellersAnalyst/DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
Frank SellersAnalyst/DeveloperAuthor Commented:
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.
Frank SellersAnalyst/DeveloperAuthor Commented:
BTW, Dave...

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

Also, thanks for the link.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank SellersAnalyst/DeveloperAuthor Commented:
Wow! Great one-step solution! Thanks!
dlmilleCommented:
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
dlmilleCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.