 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Excel data merging across excel files and matching field data
I have two excel files each has unique data 1st file: SS, Last Name, First Name... 2nd file: Last Name, First Name, Employee Number I need to copy Employee Number from the 2nd file to the corresponding employee. An employee may be listed more than once in file 1. Thank you , Lorinc EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com I would assume that the possibility that you might have a Tom Jones *and* a John Jones does exist, so you should check *both* first and last names. Enter this *array* formula in D2 of Sheet1: =INDEX(Sheet2!C$2:C$20,MATCH(1,(Sheet2!A$2:A$20=B2)*(Sheet2!B$2:B$20=C2),0))  SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. *After* the CSE entry, copy down to D20. Adjust ranges to suit your datalist. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I have two excel files each has unique data 1st file: SS, Last Name, First Name... 2nd file: Last Name, First Name, Employee Number I need to copy Employee Number from the 2nd file to the corresponding employee. An employee may be listed more than once in file 1. Thank you , Lorinc EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com It may be worth checking if names are repeated in the list. This formula should give the last match in the list which you can compare with the formula above which gives the first match: =LOOKUP(2,1/(Sheet2!A$2:A$20=B2)/(Sheet2!B$2:B$20=C2),Sheet2!C$2:C$20?) Lorinc wrote: > I have two excel files each has unique data > 1st file: SS, Last Name, First Name... [quoted text clipped - 6 lines] > EggHeadCafe.com - .NET Developer Portal of Choice > http://www.eggheadcafe.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.