Comparing 2 databases

Use the Pivot Table feature. I have had much experience and success w/ this same exact sort of thing.


DS

Is there any way I can post a sample file?

I use the Pivot feature for other databases, but I don't think it's a solution here.

I use this all the time just for the reason you stated.

Let me ask you a couple of followup questions.

Are the two files identical in their raw/initial format?
Are you looking to for unique records?
Are you looking to combine the item records?
Are you looking to correct potentially erroroneous records?

DS

If DB2 is NOT in ascending order...

=ISNUMBER(MATCH(A2,DB2!$A$2:$A$400,0))+0

If DB2 is in ascending order...

=(LOOKUP(A2,DB2!$A$2:$A$400)=A2)+0

A result of 1 means: A2 of DB1 is present in DB2, 0 it's not.

Aladin,
In your first answer, the item records can be in any sort of order in either of the files?

DS

Aladin,
In your first answer, the item records can be in any sort of order in either of the files?

DS

Click to expand...

Yes. However, it's the order of DB2 that is relevant.

I will have to try this out....

Rwallage:
Try Aladin's, I am sure this will produce the result you are looking for. I am curious about it myself.
But if you like you can try the method I proposed, as I have found it to be very helpful in many ways I never foresaw initially.
As I will be glad to assist you here.


ds

The best way, IMO, is via a query table. No formulae. Uses very powerful database functionality. Easily handles tens of thousand of records.

The files are assumed to have been saved. Within each file give each data range a defined name. Not dynamic. Headers to all fields.

For simplicity (cause I don't want to 'write a book') I'll assume the data ranges are in the one workbook and the results is returned to that file. (They don't have to be. The two source data files can be closed and the result in a new file.)

I'm going to assume table names Table1 and Table2.

Via menu data, import external data, new database query, Excel files. Select the file and then in the wizard select Table1 fields FamilyName, FirstName & Title. Same three fields also from Table2.

You get a message about the wizard can't continue & you must join the tables. Hit OK. MS Query opens. Where you see the images of the tables, click on the FamilyName field in one table and drag it to the corresponding on in the other table. Release the mouse button to create the join. Repeat for the other two fields.

The result set you see below is what you'll have as an answer. The fields are there twice each. You can remove one set of three to leave what you really need - either select the whole field and then Records, Remove Column OR hit the SQL button and edit out extra the names after where it says SELECT.

You might have duplicate entries. If you only want unique instances, either View, Query Properties, Unique Values Only. OR, in the SQL directly enter DISTINCT after SELECT. So as to have SELECT DISTINCT ...

Put the result set in Excel with either File, Return Data to Excel, OR, use the 'door' button on the toolbar.

It is simple to do and can be very powerful. Quick. Works on closed files. Etc, etc.

Like a pivot table, you need to refresh it.

This is a quick outline. Please try it. It is good.

Fazza

I use this all the time just for the reason you stated.

Let me ask you a couple of followup questions.

Are the two files identical in their raw/initial format?
Are you looking to for unique records?
Are you looking to combine the item records?
Are you looking to correct potentially erroroneous records?

DS

Click to expand...

The layout of the two spreadsheets is identical (family name, first name, tittle). The "base" datasheet is much smaller then the variable sheet. I need to know if in the variable sheet there are names that also appear in the base sheet.