A real headache

[zombeese]

The company needs me to merge two spreadsheets from two different departments
and put them into a database all can use. Both spreadsheets have over 15,000
records.

The first speadsheet has company, address, and some accounting info. The
second spreadsheet has company plus various sales data.

The problem is the departments call the same company by different names.

"XYZ Company" vs "XYZ Co."

Is there any easy way to merge these.

Right now I plan on doing a lot of cutting and pasting, but that will to
long.

Help!!!!!

 

[Tom Ellison]

Dear Zomb:

There is no way to predict how many different ways various users may
abbreviate or mis-spell things. The only real solution would have been to
unify this effort before it began.

You could minimize the difficulty of matching the lists.

1. Check the lists for duplicates and remove the duplicates from each list.
In doing this, there may be data in other columns that also do not match.
Figuring out which list has the best phone number or contact name or other
value that does not match between the lists will be a constant problem.

2. First, match those that actually do match and remove them from the
lists.

3. Alphabetize the lists and place them side by side. I would do this in
Excel, with some programming. I recommend making a drag-and-drop interface.
Drag and drop a value from the "left side" list to a value in the right
side. Record this pairing in another spreadsheet. This will be your
"equivalence" list.

4. As in step one, reconcile address, phone, contact, and other differences
to be included in the final result.

While it is still a largely manual process, it is probably the only good way
to do it. The computer can provide support, but someone knowledgable must
reconcile all the discrepancies.

Not a very pretty situtation, I would agree. There is a point, a moral.
Fully integrated computer support is a great idea. It must be consistently
and broadly implemented from a point as early as possible. Coming along to
add a consistent, integrated system later on will be expensive.

The longer these problems continue, the worse they will become.

Tom Ellison

 

[KARL DEWEY]

The easiest way I can think of is the create a temp table of companies with
the names as primay key. Add another field for update name. Append from the
first and then second dataset. open the table and copy and paste the best
name in the update field. This will build a conversion table for you.

I think you will need three tables - company, accounting, and sales.
Accounting, and sales tables will have the a foreign key relating to the
company table primary key.

 

[Jeff Boyce]

An alternative to building the interface Tom mentions would be to pile all
the names (and associated IDs/other fields) into one big table. Sort by
CompanyName. Decide (and mark) which ones you'll be connecting (you could
add a temporaryID field to each list before loading them up, then put the
same # in the one's you'll want to use queries later on to connect)

While not 100%, names that start out similar will be close to each other.

NOTE: This will NOT help you resolve "matches" like:

International Business Machines
IBM, Corp.

Regards

Jeff Boyce
Microsoft Office/Access MVP