Merge two files no single unique identifier

[Kenneth C. Benson]

I need to add the columns from one Excel file to another Excel file. The
problem is that file A has 2506 rows and file B has 1825. The "missing"
681 rows in file B are spread out throughout the file, so if I just
paste everything from file B into file A, the rows don't align.

I need a way to merge the two files, adding empty entries to the file B
information so that every row aligns the correct information from both
files.

To make it more difficult, there is no single unique identifier from
each file. Multiple entries in one file can have the same Municipality
ID, and multiple entries can have the same School District ID, but no
entry can have *both* the same Muni and SD ID.

I'm guessing I first need a way to create a unique identifier by
combining information from Muni and SD IDs, then I need to pull
information from file B into file A based on the combined identifier,
but I have almost no idea how to get started on either task.

I am an Excel novice (can you tell?), so please go slow.

Thanks,
Ken Benson

 

[Kenneth C. Benson]

No one wants to touch this?

I found a plug-in, Merge Tables Wizard, that seems to do what I want,
mostly. It "found" 1775 rows from the Excel file with 1825 rows in it,
which means that for some reason it didn't find 50 rows.

So now I need to figure out a way to compare part of the merged file (I
think, by saving to a new file and cutting out the File A stuff) with
the File B 1825-row file and find out which 50 rows it skipped and why.

Any ideas? I'm probably not being too clear.

Ken Benson

 

[Dave Peterson]

Maybe you could concatenate the two key columns into a new column A, then use
=vlookup() to look for a match between those two columns and return the values
from the other columns.

You could insert a new column A, then if the key columns are X and Z, you could
use this in column A:

=x1&"..."&z1
and drag down.

Debra Dalgleish has lots of notes on =vlookup() here:
http://www.contextures.com/xlFunctions02.html

 

[Kenneth C. Benson]

Thanks, Dave

I finally figured out that I'm working with two subsets of a larger
file. IOW, even though File A had more entries than File B, there were
50 entries in File B that weren't in File A, and 731 entries in File A
that weren't in File B.

So I used Merge Tables Wizard to merge File B into File A and then I
went through the merged file and found (just by looking) the 50 entries
that were in File B that didn't make it into the merged file. Now I'm
copying the missing File B entries, one by one, into the merged file.

There's probably a better way, but I've already been working on this all
day.

Ken Benson

 

[Dave Peterson]

I've done this in the past.

Create that unique key in each worksheet.

Copy both sets of keys to a new (third) worksheet.

Use data|filter|advanced filter to eliminate the duplicates. Now the list in
the third location is a list of unique keys from both worksheets.

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then used either =vlookup() or =index(match()) to retreive values from the
original two worksheets.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[Kenneth C. Benson]

Thanks, Dave

I've printed this out for next year. I deal with this job once a year,
so I'll need to figure this all out again in 2008.

Ken Benson