Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros-creating new sheets | Excel Worksheet Functions | |||
How do I merge information from sheets on other files into the ma. | Excel Worksheet Functions | |||
How can I merge multiple sheets from different Excel files workbo. | Excel Worksheet Functions | |||
Exporting multiple sheets to multiple htm files? | Excel Discussion (Misc queries) | |||
Cannot access read-only documents. | Excel Discussion (Misc queries) |