I have a bunch of records in an Excel spreadsheet.  Each record is a
separate line on a spreadsheet, with each field
being a separate column.  I have another bunch of records in a
second separate spreadsheet.  These records have different fields, for
the most part, except there is always at least one field that is
common to both spreadsheets.  My question is, how can combine the
records in the two spreadsheets together, using the fact that any
given two records that should be combined will always have a common
field, with the same value in each spreadsheet?  (Think of the common
field as an item ID, or as a serial number)

I will be looking for some very specific instructions on how to do
this.  You can make up a couple of dummy sheets with a couple of
records of 3 fields each to use in an example, if that helps provide a
more detailed answer.

---

Request for Question Clarification by mathtalk-ga on 26 Nov 2003 13:53 PST

Hi, chanelops-ga:

I don't know if you've had a chance to review the Comments offered
below by tar_heel_v-ga and probonopublico-ga.  I thought their
pointers were apt.

There would be a case when the Excel "spreadsheets" are actually
worksheets in the same "workbook", when I might be tempted to write
some VBA code to do the join operation, or even try to get away with a
tricky bit of cut and pasting.  Call this the "internal" approach.

Then there would be a case when the two Excel "spreadsheets" are in
different workbooks.  Although you can cut and paste to reduce this to
the previous case, one can also take the "external" approach of
treating the Excel spreadsheets as "tables" using Microsoft's ODBC
drivers for Excel.

Both of these might sound like programmer sorts of solutions, but
really the Microsoft Query tool will let you import data to Excel from
another Excel spreadsheet using SQL-like criterion that you develop in
a point-and-click Wizard.

If you are still interested in suggestions on this, please outline the
level of technical knowledge you feel comfortable laying claim to.  I
think your suggestion that a Researcher create a couple of sample
spreadsheets to illustrate the proposed procedures is helpful. 
However the price you've offered is consistent with a brief answer,
perhaps one link or sentence.

[Google Answers - How to price your question]
http://answers.google.com/answers/pricing.html

regards, mathtalk-ga

---

Clarification of Question by chanelops-ga on 26 Nov 2003 17:47 PST

I did look at the article suggested by tar heel, which was
interesting, but I didn't see anything that could help me.  As far as
probonopublico's comment goes, I am willing to do that.  I have a copy
of Access, but my database skills and experience are minimal.  That's
why I was trying to do it inside Excel.  (Not that I'm an Excel
wizard, either)

As far as the clarification request by mathtalk, I am not really very
proficient in programming, and would like to avoid a lot of Excel
macros, for example.  The ODBC query approach sounds interesting, but
also a bit daunting.  I agree that apparently I am looking for more
than a $5.00 answer, but I really don't know how much more, since I
don't know the level of effort that would be required.  Mathtalk, if
you can let me know what you think is a reasonable amount, I will take
changing the price under consideration.

Oh, one more thing.  I will be off-line until Saturday.  I hope
everyone has a happy Thanksgiving.

This may be of some interest:
Using Excel as a Database
http://www.pcmag.com/article2/0,4149,760893,00.asp

Why not export each spreadsheet to a relational database; then relate
the two databases .... etc.?

Finally, export the combined database to a spreadsheet?

First, I just want to make sure that I understand what you are trying to do

If you are trying to use Excel as some sort of relational database
then you can all but forget it

If you are trying to merge the two sets of data into one so that you
have a new flat file database then, this is easy.

Just use the vlookup function
http://www.bradford.ac.uk/acad/envsci/infostore/Info%20Files/Webs/excel/MoreFunctions/vlookup/vlookup.html

What this will allow you to do is look up one value (your key value in
one column in 1 spreadsheet) in another column in another spreadsheet
and then return the value from another column in the second
spreadsheet.

So, for example, spreadsheet 1 contains data in columns A, B, C & D

Spreadsheet 2 contains data in columns G, H, I, J

The matching key values are held in columns A and column G

Spredsheet 2 has been sorted with column G in ascending order

You want the data that appears in columns H, I and J to appear in
columns E, F & G in spreadsheet1

So, in cell E1 we would enter 

=VLOOKUP(A1,[Spredsheet2]Sheet1!$G$1:$J$28,2,FALSE)

where $G$1:$J$28 is the range of our data in spreadsheet 2

We can then copy this formula all the way down comlun E

Then, in cell F1 in spreadsheet 1 we can enter the formula

=VLOOKUP(B1,[Spreadsheet2]Sheet1!$G$1:$J$28,3,FALSE)

Notice here we now reference cell B1 in Spreadsheet 1 and return the
matching value in the 3rd column along in Spreadsheet 2

The word False at the end of the formula forces it to find an exact match

Now, in this way you can easily transfer / merge the data from one
spreadsheet into another one so that you have one flat file database

I have assumed that you only have data in the sheets (hence B1, C1 etc)

If you have field names present or whatever then you will need to
adjust the formula appropriately (i.e. B2, C2 etc)

Now, that was solution 1

Solution 2 presumes that you want to keep your original data in
separate spreadsheets and merge them together, dynamically, into one
new spreadsheet with this 3rd spreadsheet being updated dynaicalyy as
and when data is added to your first 2 spreadsheets.

To do this, in each of your first 2 spreadsheets, you can define a
name for each data range (select the data and then click on Insert |
Name | Define in Excel).

You can then create an odbc link using excel drivers to each separate
spreadsheet and then go into your 4rd spreadsheet and choose Data |
Import External Data | New Database Query.

USing this tool, which I wont go into here, you can then create a
query which links these 2 external data sources together and allows
you to pull back data into your 3rd spreadsheet, laying it up how you
want, applying whichever filters you need and whichever formatting you
desirte.

I say I wint tell you how to do that here, merely because I think
you've got more than $ 5 worth as is and I've basically told you how
to do it so long as you have a rudimentary knowledge of excel and
Microsoft Query.

Anyway, hope this helps

If I have misunderstood your requirements entirely, please clarify
them and I'll suggest something else for you

Best Regards

oh yeah, if your data in your second spreadsheet is arranged
horizontally rather than vertically, you can always use the Hlookup
function

Philf, you have given me just what I was looking for!  I tried it on a
small test sheet, and it seems to work fine, so I have no reason to
think that it won't work with the larger sheets.

Since you did not post it as an answer, I don't see any way to pay
you.  I assume you are not a GA researcher, and that's why you didn't
post it as an answer.  If I'm wrong, and you are a researcher, post it
as an answer and I will make sure you get paid.

In either case, thanks!