ExcelBanter - Querying 2 Excel files

On Mon, 24 Apr 2006 05:56:02 -0700, J-Unit
wrote:

I have 2 seperate excel files where I need to compare data in one with data
in the other file.

A further twist is I need to match the Forename, Surname and date of
distribution which are fields in both files.

Firstly is it possible to compare or query data in one excel file with
another excel file and if so could anyone give me an example to work to

Secondly is it possible to query multiple fields for matching records across
these 2 files again an example would be greatly appreciated if this is
possible.

I would appreciate any help on this.

I assume you're trying to identify where a record exists in one book
but not another.

Have you tried VLOOKUP?

=VLOOKUP(A1,[Book1]Sheet1!$A$1:$C$100,1,FALSE)

Where A1:C100 contains your three fields of data, Forename,Surname and
date. The example above is checking a Forename in A1 of say Book2,
with all the forenames in column A of Book1.

The standard approach for matching multiple fields is to use a helper
column. Add a new column A in Book1, and concatenate the first three
fields together into a single string. i.e.

=B1&C1&D1.

A1 is now a unique identifier.

Now do the same in Book2, and in say E1 of this book put your vlookup
formula.

i.e.

=VLOOKUP(A1,[Book1]Sheet1!$A$1:$A$100,1,FALSE)

This will result in either a unique name or #N/A. The #N/As identify
records that don't exist.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________