append 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

Gaurav
Thu May 08 09:14:36 CDT 2008

See VLOOKUP in Help.


"Mark B" <MarkB@discussions.microsoft.com> wrote in message
news:7B83C741-AA60-47C4-B497-86F34D16912D@microsoft.com...
>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
>

Rick
Thu May 08 10:00:21 CDT 2008

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


"Mark B" <MarkB@discussions.microsoft.com> wrote in message
news:7B83C741-AA60-47C4-B497-86F34D16912D@microsoft.com...
>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
>

Rick
Thu May 08 12:19:25 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:ONuM1wRsIHA.1768@TK2MSFTNGP03.phx.gbl...
> 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
>
>
> "Mark B" <MarkB@discussions.microsoft.com> wrote in message
> news:7B83C741-AA60-47C4-B497-86F34D16912D@microsoft.com...
>>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
>>
>