HELP finding duplicates in two data sets

[NJHOLLO]

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.

 

[BGY23]

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

 

[jbeaucaire]

Wth the data as you've described one sheet:

Excel Workbook
	A	B	C	D
1	CustID	OrigBalance	SignBalance	EquBalance
2	ID001	100.00	75.00	25.00
3	ID002	200.00	150.00	50.00
4	ID003	300.00	225.00	75.00
5	ID004	400.00	300.00	100.00
6	ID005	500.00	375.00	125.00
7	ID006	600.00	450.00	150.00
8	ID007	700.00	525.00	175.00
9	ID008	800.00	600.00	200.00
10	ID009	900.00	675.00	225.00
11	ID010	1000.00	750.00	250.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
	A	B	C	D
1	CustID	OrigBalance	SignBalance	EquBalance
2	ID002	200.00	150.00	50.00
3	ID022
4	ID033
5	ID044
6	ID055
7	ID006	600.00	450.00	150.00
8	ID077
9	ID010	1000.00	750.00	250.00
10	ID023
11	ID001	100.00	75.00	25.00
Sheet3

 

[NJHOLLO]

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

_____________
NJHOLLO