combining 2 files with common data

Posted by j ross on December 05, 2001 8:03 AM

I have two files that have one common field (column) between them. I want to combine these 2 files into one (will not be including all columns) by matching data in their common field. For example:

Workbook 1:

Location field pipe
00/23-222-98/W04 North 099
... ... ...

Workbook 2:
Location Owner Date
00/23-222-88/W04 Nova March
00/23-222-98/W04 Alli. March
... ... ...

I would then want to link the common location and create a new workbook (or worksheet) with Location, Owner, and Field as columns.

Thanks in advance
:) Jill

Posted by Aladin Akyurek on December 05, 2001 8:46 AM

Jill --

Assuming that there are no locations in one file that does not exist in another file, I'd suggest creating a copy of Workbook 2 and give it a new name.

Open Workbook 1.
Open Workbook 3.
Delete the Date column from 3.
Enter in the 3rd cell in the first row of data:

=VLOOKUP(A2,[Workbook1.xls]Sheet1!$A$1:$A$1000,2,0)

The above formula assumes that A2 contains the first location value and this formula is entered in C2 (in Workbook 3).

Activate C2 and dubble click on the little black square.

As everything goes right, you'll get all associated Field-values in column C.

You can select all of the cells of C, copy it, and do a Edit|Paste Special ->Values to get rid of the VLOOKUP-formulas.

Hope this helps.

Aladin

=========

Posted by j ross on December 05, 2001 9:37 AM

Thanks Aladin, You are a god sent.

I am having problems because doesn't this assume there will be an exact match between the 2 files? I really want the vlookup to take the Project # value in the Workbook 1 and go to Workbook 2 and find it's match. When it has done that I want certain columns from Workbook 1 and 2 to create Workbook 3 based on a common Project #.

Does that make sense???

Posted by Aladin Akyurek on December 05, 2001 10:32 AM

Jill --

Would you do the following:

Open Workbook 1, go to the relevant sheet, activate an empty cell, type =, select 10 rows of your auction data, and hit CONTROL+SHIFT+ENTER at the same time. Activate this cell again, got to the Formula Bar, select the formula, hit F9, copy what you see between two braces and paste it in the follow up post.

Open Workbook 2 and carry out the same proc just described.

Aladin

=======

Posted by Bariloche on December 05, 2001 9:07 PM

Jill,

My first inclination would be to put your tables into MS Access. I'm sure you'll think its a lot of trouble, but if you have MS Access and import these two "files" I think you'll find that Access accomplishes this task in the blink of an eye.

I recognize that most people are intimidated by Access, but its really not that difficult. Quite a few things that people use Excel for, Access can do better and faster.

If you want to give it a try, post back here and I'll walk you through the steps. Seriously, it will take more time for me to type out the instructions than it would take for me to actually do it. What you are asking for is a trivial task for a database, it is actually what databases do as a matter of routine.

take care