Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Spreadsheets and Finding Matches and Exceptions
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Spreadsheets and Finding Matches and Exceptions
On 26 Jan, 00:36, reganbrown
wrote: 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/v...ermediate.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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Spreadsheets and Finding Matches and Exceptions
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!! "Matt Richardson" wrote: On 26 Jan, 00:36, reganbrown wrote: 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/v...ermediate.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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Spreadsheets and Finding Matches and Exceptions
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 "reganbrown" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding matches ( | Excel Discussion (Misc queries) | |||
Finding matches (in Excel 2007) | Excel Discussion (Misc queries) | |||
finding exact matches | Excel Worksheet Functions | |||
finding exact matches using vlookup | Excel Worksheet Functions | |||
Function to find only exact matches in spreadsheets? | Excel Worksheet Functions |