Posted to microsoft.public.excel.worksheet.functions
|
|
append worksheet with data from another
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
"Rick Rothstein (MVP - VB)" wrote in
message ...
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(",",W S2!B$1:B$5)))*ROW(B$1:B$5))),LEFT(A1,FIND(",",A1)-1),"")
Rick
"Mark B" wrote in message
...
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
|