Matching two files or sheets

[RayB]

Hi! Can someone help me!

Thanks in advance.

I have two worksheets that have one field in each that may match. Each worksheet has a different number of rows. They are not sorted and some cells may not contain any data. Need to match and sort by column D in this example.

Example
(Hyphen indicate columns for clarity)

Sheet 1

A -B -C -D

234 -widget- 44 -LD2M34
567 -gadget -76- LD2M76
789 -top -39- LD2M95


Sheet 2

A -B -C -D

BLDG1 -75- 99- LD2M95
BLDG4 -45 -27 -LD2M34

Desired Output in third worksheet

A -B -C -D -E- F- G -H
234 -widget- 44 -LD2M34- LD2M34 -BLDG4 -45 -27
789 -top -39 -LD2M95 -LD2M95 -BLDG1 -75 -99
567- gadget -76- LD2M76

[Nika Lampe]

Hi,

you can get the data by using MS Excel Query (menu Data - Import External
Data - New database query).
Select one worksheet (if you do not see sheets, then select Options and
enable "System tables"). In the last step select "View data or edit query in
Microsoft Query".
Then add the second worsheet (menu Table - Add tables) and then connect them
by dragging mouse from field in one worksheet to the same field in second
worksheet.
And then double click fields you want in "combined table".
To return (display) data in Excel, select File - Return data to Excel.

Regards,
Nika Lampe

"RayB" wrote:

>
> Hi! Can someone help me!
>
> Thanks in advance.
>
> I have two worksheets that have one field in each that may match. Each
> worksheet has a different number of rows. They are not sorted and some
> cells may not contain any data. Need to match and sort by column D in
> this example.
>
> Example
> (Hyphen indicate columns for clarity)
>
> Sheet 1
>
> A -B -C -D
>
> 234 -widget- 44 -LD2M34
> 567 -gadget -76- LD2M76
> 789 -top -39- LD2M95
>
>
> Sheet 2
>
> A -B -C -D
>
> BLDG1 -75- 99- LD2M95
> BLDG4 -45 -27 -LD2M34
>
> Desired Output in third worksheet
>
> A -B -C -D -E- F- G -H
> 234 -widget- 44 -LD2M34- LD2M34 -BLDG4 -45 -27
> 789 -top -39 -LD2M95 -LD2M95 -BLDG1 -75 -99
> 567- gadget -76- LD2M76
>
>
> --
> RayB
> ------------------------------------------------------------------------
> RayB's Profile: http://www.excelforum.com/member.php...o&userid=33499
> View this thread: http://www.excelforum.com/showthread...hreadid=560669
>
>

[Terry Tipsy]

A simpler solution may be to use the 'vlookup' function. This function
allows you to use a common field, in your example column D, to link
information. One criteria for 'vlookup' is for the 'look-up' data to have
the common field in the first column. You will need to rearrange your data
to put column D in Column A.
--
T Tipsy


"RayB" wrote:

>
> Hi! Can someone help me!
>
> Thanks in advance.
>
> I have two worksheets that have one field in each that may match. Each
> worksheet has a different number of rows. They are not sorted and some
> cells may not contain any data. Need to match and sort by column D in
> this example.
>
> Example
> (Hyphen indicate columns for clarity)
>
> Sheet 1
>
> A -B -C -D
>
> 234 -widget- 44 -LD2M34
> 567 -gadget -76- LD2M76
> 789 -top -39- LD2M95
>
>
> Sheet 2
>
> A -B -C -D
>
> BLDG1 -75- 99- LD2M95
> BLDG4 -45 -27 -LD2M34
>
> Desired Output in third worksheet
>
> A -B -C -D -E- F- G -H
> 234 -widget- 44 -LD2M34- LD2M34 -BLDG4 -45 -27
> 789 -top -39 -LD2M95 -LD2M95 -BLDG1 -75 -99
> 567- gadget -76- LD2M76
>
>
> --
> RayB
> ------------------------------------------------------------------------
> RayB's Profile: http://www.excelforum.com/member.php...o&userid=33499
> View this thread: http://www.excelforum.com/showthread...hreadid=560669
>
>