If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A real headache
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!!!!! |
#2
|
|||
|
|||
A real headache
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 "zombeese" wrote in message ... 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!!!!! |
#3
|
|||
|
|||
A real headache
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. "zombeese" wrote: 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!!!!! |
#4
|
|||
|
|||
A real headache
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 "zombeese" wrote in message ... 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!!!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word Forms: Inclusde small business and standard real estate | Buciak | General Discussion | 2 | January 9th, 2005 06:16 PM |
Real Time Data Entry | JD | Setting up and Configuration | 2 | October 24th, 2004 12:11 PM |
Real Time Charting | JD | Charts and Charting | 0 | October 19th, 2004 12:15 PM |
Hide my real email address | Opinicus | Outlook Express | 2 | August 27th, 2004 02:59 AM |