MS Excel database question

January 22, 2006 2:02 PM

Basic Microsoft Excel question. How do I join two databases together when they share elements but are not coextensively equal? (More)

I have a database containing (for instance) a set of license plates and their corresponding owners and phone numbers. I have a second similar database, containing license plates and corresponding make of cars but not owners or phone numbers. How do I join the two databases together?

This would be an easy question for me to answer if the two lists of license plate numbers were identical. I'd just sort them so they would be in the same order and cut and paste. But they aren't identical -- there are some license plates in both lists, but also some that are only in the first list and some that are only in the second list. What sort of commands could I use to merge the two databases into one giant database that contains all the information?

posted by Mr. Justice to computers & internet (8 comments total)

first of all, if you are working in databases then you should use a database application like Microsoft Access. Excel spreadsheets contain tabular data, but you do not have the flexibility of a relational structure that makes it possible to perform "queries" to your data that will produce the results that you are wanting.

put your 2 data sets into 2 tables into Access. This can basically be performed by a simple copy/paste, but you should also add an auto increment (or auto number) field to serve as a unique identifier for all the rows in each table.

next, create a new query and with the query builder you should be able to "merge" the two by linking the license column and setting the properties of the join appropriately.

If you get stuck, let us know where and I can give you some SQL that will do what you need. Learning SQL can be a pain but man does it come in handy when you need to do something like this. The Query Builder in Access makes defining a data relationship and querying specific data to produce a single 2-d table very easy and in a visual way.
posted by nickerbocker at 2:43 PM on January 22, 2006

Excel is easily capable of this sort of thig.

Say your tables are in columns A:B on each of two pages (Sheet1 and Sheet2, say).

On each page in cell C1 enter:

=vlookup(A1,Sheet2!A:B,2,0)

(where Sheet1 is the name of the other sheet) and then fill down.

Column C in each page will now either contain the corresponding make/model or owner, or will contain "#N/A".

Then sort for the ones that come up "#N/A". These are not in both tables, so you can grab them out and copy from one to the other.
posted by pompomtom at 3:46 PM on January 22, 2006

(On preview, pompomtom beat me to the VLOOKUP recommendation, but I'll post what I wrote anyway...)


It is indeed much easier to do this in Access than Excel. But the Excel function VLOOKUP can be used to join the tables in very specific circumstances.

I'll make up an example, and maybe you can see if it works with your data.

Let's say the first Excel sheet (Sheet1) consists of license plate in cells A1:A100, names in cells B1:B100, and phone numbers in cells C1:C100. You also have a separate Excel sheet (Sheet2) that list license plates in A1:150 and makes in B1:B150.

You can use VLOOKUP to "look up" the license plate in Sheet1!A1 in the range of license plates in Sheet2. In column D1 on Sheet1, you would type:

=VLOOKUP(A1,Sheet2!$A$1:$B$150,2,FALSE)

This takes A1 and looks it up in the table range Sheet2!$A$1:$B$150, and then selects the second (2) column. Copy this down the D column, and it will look up every value in the A column.

This doesn't work too well if there are non-unique values in sheet 2, or if there are values in Sheet2 that aren't on Sheet1, but that you want to include in the results. But both of these cases can be solved by porting the data over to Access.

Not an answer, but this is a funny doodle.
posted by blue mustard at 4:01 PM on January 22, 2006

Or, if you want to avoid all of the N/A results, use this:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$150,2,FALSE)),"No Match",VLOOKUP(A1,Sheet2!$A$1:$B$150,2,FALSE))

All this does is gives you a "No Match" message whenever there is no match in Sheet2.

Also note that the "False" argument is needed in this case, as blue mustard said, so that the formula looks for exact matches only (as opposed to finding the closest match that is equal to or higher than the lookup value).
posted by crapples at 8:00 PM on January 22, 2006

Note: VLOOKUP assumes that the list being searched is already sorted (ascending). You're joining the two lists by license plates; if you don't sort on that column, you'll have lots of "No Match" results.
posted by WestCoaster at 9:10 PM on January 22, 2006

vlookup and access are probably the simplest suggestions. I'd say that if your looking to do more complex tasks with excel it would probably be useful to get a book on VBA for excel (i used to teach with this one). VBA is a simple programming language with a relatively straight forward syntax. It's no C, but it's good if you need to write something quickly to organize/calc data.
posted by NGnerd at 9:14 PM on January 22, 2006

Westcoaster: VLOOKUP does not assume the list is sorted. That is only for range lookups, where the final term is 1 (or TRUE), because using range lookups doesn't make any sense if the table you're using is unsorted.

This example is not a range lookup.
posted by pompomtom at 9:48 PM on January 22, 2006

I stand corrected - the list needs to be sorted only if the third/final argument/term in the function is FALSE (or 0, or there are only two arguments/terms used in the function, since FALSE is the default).
posted by WestCoaster at 11:29 AM on January 24, 2006