Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and insert from one workbook to another
Hello,
I have a project to match SSN's between 2 workbooks and insert columns from one sheet to another. Example: File1 Column A = SSN, Col B = Salary, C = DOB File2 Col A = SSN, B = Address, C = State, D = Zip I need to take SSN from file2 and find it in file1. If found match then copy and paste columns B-D of file2 to end of record on file1. Is there a formula for this type thing. -- Thank You! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and insert from one workbook to another
A simple set of Vlookup formulas will do the job.
Assumptions: Datalist on Sheet1 starts in Row2, from A2 to C(whatever). Datalist on Sheet2 starts in Row2 and goes from A2 to D100. On Sheet1, enter this formula in D2: =IF(ISNA(MATCH($A2,Sheet2!$A$2:$A$100,0)),"",VLOOK UP($A2,Sheet2!$A$2:$D$100,COLUMNS($A1:B1),0)) Copy across to F2, Then select D2:F2, and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "maijiuli" wrote in message ... Hello, I have a project to match SSN's between 2 workbooks and insert columns from one sheet to another. Example: File1 Column A = SSN, Col B = Salary, C = DOB File2 Col A = SSN, B = Address, C = State, D = Zip I need to take SSN from file2 and find it in file1. If found match then copy and paste columns B-D of file2 to end of record on file1. Is there a formula for this type thing. -- Thank You! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and insert from one workbook to another
In column D, row 2 of File 1, use a formula such as the one below:
=VLOOKUP($A2,File2!$A:$D,2,FALSE) in column E: =VLOOKUP($A2,File2!$A:$D,3,FALSE) in column F: =VLOOKUP($A2,File2!$A:$D,4,FALSE) These formulas will use the value in A2 to look for in range A:D of File 2. If a match is found, it will return the record from the different columns matching the criteria. You will notice that the only difference in the formulas is the 2, 3, and 4. This is the Column Index number and tells the formula which column from the lookup array (A:D) to return the value from. maijiuli wrote: Hello, I have a project to match SSN's between 2 workbooks and insert columns from one sheet to another. Example: File1 Column A = SSN, Col B = Salary, C = DOB File2 Col A = SSN, B = Address, C = State, D = Zip I need to take SSN from file2 and find it in file1. If found match then copy and paste columns B-D of file2 to end of record on file1. Is there a formula for this type thing. -- Thank You! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and insert from one workbook to another
Thanks RagDyer,
Your answer works like a charm. There was one thing I forgot to tell you, which I can take care of, is that some SSN's have duplicates on File1 which I guess your formula will take the first one found? Anyway, it doesn't matter because I will do a quick find match and eliminate the duplicates before running your formula. Also, your formula specifies sheet 1 and sheet 2 so I put the two worksheets in the same file but if I need to use your formula for two seperate workbooks, can you tell me where in your formula to put the names of the workbooks? File1 and File2. Another minor thing is that this formula will not get row A (headers) but again this is not the formula's fault because row A doesn't have a SSN. I will just copy paste and do a quick eyeball test. Thanks a lot, -- Thank You! "RagDyer" wrote: A simple set of Vlookup formulas will do the job. Assumptions: Datalist on Sheet1 starts in Row2, from A2 to C(whatever). Datalist on Sheet2 starts in Row2 and goes from A2 to D100. On Sheet1, enter this formula in D2: =IF(ISNA(MATCH($A2,Sheet2!$A$2:$A$100,0)),"",VLOOK UP($A2,Sheet2!$A$2:$D$100,COLUMNS($A1:B1),0)) Copy across to F2, Then select D2:F2, and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "maijiuli" wrote in message ... Hello, I have a project to match SSN's between 2 workbooks and insert columns from one sheet to another. Example: File1 Column A = SSN, Col B = Salary, C = DOB File2 Col A = SSN, B = Address, C = State, D = Zip I need to take SSN from file2 and find it in file1. If found match then copy and paste columns B-D of file2 to end of record on file1. Is there a formula for this type thing. -- Thank You! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and insert from one workbook to another
A lazy and *accurate* way to insert the path is to let XL do it for you.
Open both workbooks. Click in the D2 cell containing the formula. In the *formula bar*, select the first: Sheet2!$A$2 Navigate to the other file and click in A2 (or whatever cell the datalist starts in). You'll see that XL filled in all the info for you. Now click in the second Sheet2!$A$2 And do the same thing. Hit <Enter Now click back into D2 and edit and /or revise your range locations and sizes if necessary. This formula is now ready to be copied across and then down as before. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "maijiuli" wrote in message ... Thanks RagDyer, Your answer works like a charm. There was one thing I forgot to tell you, which I can take care of, is that some SSN's have duplicates on File1 which I guess your formula will take the first one found? Anyway, it doesn't matter because I will do a quick find match and eliminate the duplicates before running your formula. Also, your formula specifies sheet 1 and sheet 2 so I put the two worksheets in the same file but if I need to use your formula for two seperate workbooks, can you tell me where in your formula to put the names of the workbooks? File1 and File2. Another minor thing is that this formula will not get row A (headers) but again this is not the formula's fault because row A doesn't have a SSN. I will just copy paste and do a quick eyeball test. Thanks a lot, -- Thank You! "RagDyer" wrote: A simple set of Vlookup formulas will do the job. Assumptions: Datalist on Sheet1 starts in Row2, from A2 to C(whatever). Datalist on Sheet2 starts in Row2 and goes from A2 to D100. On Sheet1, enter this formula in D2: =IF(ISNA(MATCH($A2,Sheet2!$A$2:$A$100,0)),"",VLOOK UP($A2,Sheet2!$A$2:$D$100,COLUMNS($A1:B1),0)) Copy across to F2, Then select D2:F2, and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "maijiuli" wrote in message ... Hello, I have a project to match SSN's between 2 workbooks and insert columns from one sheet to another. Example: File1 Column A = SSN, Col B = Salary, C = DOB File2 Col A = SSN, B = Address, C = State, D = Zip I need to take SSN from file2 and find it in file1. If found match then copy and paste columns B-D of file2 to end of record on file1. Is there a formula for this type thing. -- Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to use INDEX and MATCH to insert a value w/ multiple criter | Excel Worksheet Functions | |||
Trying to match certain fields from 1 workbook to another | Excel Discussion (Misc queries) | |||
I want to match and insert info on 2 different sheets | New Users to Excel | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
Index/Match to look up a value in one workbook and insert it into. | Excel Discussion (Misc queries) |