HELP finding duplicates in two data sets

NJHOLLO

New Member
Hi everyone,

I have two sets of data and I need to compare them and extrapolate the duplicates to a new sheet. The first data set conatins columns for customer ID, original balance, sign balance and equipment balance. The second data set is just Customer ID's. I will be looking for similarities in the customer ID column however, I would like to include all of the fields from the first data set for any of the duplicate entries. There will be about 800 customer id's in the second data set. Any advice on formulas or functions for this would be appreciated.

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
In the list of Customer ID's, your second data set, use a vlookup to look at the customer Id's in your first data set. Any that return a Customer Id already exist in the first data set and any that return #N/a are not in the first data set.

Filter the second data set so you only have #N/A's and add these to your first data set. You will now have a list of all Customers and the additional columns will already be there.

Good Luck

Wth the data as you've described one sheet:

Excel Workbook
ABCD
1CustIDOrigBalanceSignBalanceEquBalance
2ID001100.0075.0025.00
3ID002200.00150.0050.00
4ID003300.00225.0075.00
5ID004400.00300.00100.00
6ID005500.00375.00125.00
7ID006600.00450.00150.00
8ID007700.00525.00175.00
9ID008800.00600.00200.00
10ID009900.00675.00225.00
11ID0101000.00750.00250.00
Sheet2
This one formula (highlighted in the sample below) copied down and over will bring over the matching data for the duplicate IDs.

Excel Workbook
ABCD
1CustIDOrigBalanceSignBalanceEquBalance
2ID002200.00150.0050.00
3ID022
4ID033
5ID044
6ID055
7ID006600.00450.00150.00
8ID077
9ID0101000.00750.00250.00
10ID023
11ID001100.0075.0025.00
Sheet3

Thank you both. Both of these methods are very sweet and I learned how to use a couple of new formulas today!

_____________
NJHOLLO

Replies
3
Views
125
Replies
0
Views
150
Replies
1
Views
55
Replies
18
Views
672
Replies
17
Views
325

1,206,921
Messages
6,075,577
Members
446,147
Latest member
homedecortips

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back