 |
 | 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 / Worksheet Functions / May 2008append worksheet with data from another
I have 2 worksheets that I need to bring together into a single worksheet. The first column of each worksheet is the "persons" ID number. I want to append the data in worksheet 1 with the data in worksheet 2, based upon the ID number. The data in worksheet 2 is a mix of text, number, phone number, etc. Is there a single formula I can use to bring it all in based upon the ID numbers in column A? example... WS1 WS2 123,Mark 157,trans,(201)646,2008 Event,Yes 124,Harry 123,mortg,(732)995,2008 Event,No 129,Dave 762,rund,(676)423,2007 Event,Maybe 129,mortg,(212)123,2008 - Trip,Yes 124,party,(919)222,2006 - Trip,No DESIRED RESULT IN WS1 123,Mark,mortg,(732)995,2008 Event,No 124,Harry,party,(919)222,2006 - Trip,No 129,Dave ,mortg,(212)123,2008 - Trip,Yes See VLOOKUP in Help. >I have 2 worksheets that I need to bring together into a single worksheet. > The first column of each worksheet is the "persons" ID number. [quoted text clipped - 22 lines] > 124,Harry,party,(919)222,2006 - Trip,No > 129,Dave ,mortg,(212)123,2008 - Trip,Yes Here is what I came up with... assuming your data on WS1 is in Column A and your data in WS2 is in Column B (I chose different columns so you could see what belong to which worksheet), this formula (assumed to be placed on WS1) should return what you are looking for... =A1&SUBSTITUTE(INDEX(WS2!B$1:B$5,SUMPRODUCT(--(LEFT(A1,FIND(",",A1))=LEFT(WS2!B$1:B$5,FIND(",",WS2!B$1:B$5)))*ROW(B$1:B$5))),LEFT(A1,FIND(",",A1)-1),"") Rick >I have 2 worksheets that I need to bring together into a single worksheet. > The first column of each worksheet is the "persons" ID number. [quoted text clipped - 22 lines] > 124,Harry,party,(919)222,2006 - Trip,No > 129,Dave ,mortg,(212)123,2008 - Trip,Yes Using the same data column assumptions, this formula is shorter and should be more efficient... =A1&MID(VLOOKUP(LEFT(A1,FIND(",",A1))&"*",WS2!B:B,1,FALSE),FIND(",",A1),255) Rick > Here is what I came up with... assuming your data on WS1 is in Column A > and your data in WS2 is in Column B (I chose different columns so you [quoted text clipped - 32 lines] >> 124,Harry,party,(919)222,2006 - Trip,No >> 129,Dave ,mortg,(212)123,2008 - Trip,Yes |
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.