Merging Spreadsheets and Finding Matches and Exceptions

[reganbrown]

I have two Excel spreadsheets that I wish to merge. One list has about 2600
names, the other about 800. The matching key is "user ID" How do I find the
matches between the two spreadsheets and how do I find the exceptions? The
fields are basic, first name, last name, used ID, and email address. Any
sugestions would be very helpful and save me from having to merge them
together and go line by line to figure it out.

Thanks

 

[Matt Richardson]

I have two Excel spreadsheets that I wish to merge. One list has about 2600
names, the other about 800. The matching key is "user ID" How do I find the
matches between the two spreadsheets and how do I find the exceptions? The
fields are basic, first name, last name, used ID, and email address. Any
sugestions would be very helpful and save me from having to merge them
together and go line by line to figure it out.

Thanks

Your best solution would be to use the VLOOKUP function. For an
explanation of how it works, try this link:-

http://teachr.blogspot.com/2006/07/vlookup-tutorial-excel-intermediate.html

Basically the VLOOKUP function will allow you to lookup values in one
list against another. If there's any other help you need please let me
know.

Regards,
Matt Richardson
http://teachr.blogspot.com

 

[reganbrown]

Thanks for the advice Matt. I've had very limited experience with vlookup,
but I thought vlookup was only useful for finding exact matches, I need to
find the matches and more importantly the exceptions between the two
spreadsheets.

To start with, do I copy and paste the data from the one spreadsheet into
the other before I start this process? Same sheet? Seperate tab in the same
workbook? Sorry, you must think I'm an idiot, but like anything else unless
you do things like this on a regular basis, you don't remember the process. I
am approaching that situation now.

Is there an online class I can take that will teach me this kind of stuff?
Would it be easier to do it in Access?

Thanks for your help!!

 

[Don]

The VLookup is a good option for when you have once occurrances in what you
are looking for. If you have one heading as first name, next as 2nd name and
middle init. then you can concatinate them together on both sheets / data
=a1&b1&c1, then do your vlookup.

Another option is to take the 1st set of data , insert a column and identify
this group as in file1 , then take the 2nd set of data and copy it below the
1st set of data making sure the columns line up like in first name , 2nd name
etc. Then identify in column A the 2nd set of data like File 2 so it lines
up in the same column as file1. Then do a pivot table and you can compare
them side by side.

A third option that I have done recently is cool but similar to the two
options above but sorting the file by the concatinated amounts , then you can
show the data to the right and use the filter command to collaps. This
requires single occurance in each file.

good luck