Matching two files or sheets
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 |
Matching two files or sheets
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 |
Matching two files or sheets
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 |
All times are GMT +1. The time now is 10:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com