Solved: help with excel merge (not mail merge)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

needsleep

Thread Starter
Joined
Sep 27, 2005
Messages
112
Using Excel 2003, I have names and addresses and some other information across 5 different workbooks. I'd like to be able merge these tables into a single table, preserving unique records and avoiding duplicate records. The format varies across the workbooks, but we can make it the same if needed. Tools/"compare and merge workbooks" is currently grayed out. Can you tell us how to do this?
 
Joined
Aug 5, 2005
Messages
3,086
...Not sure I agree that a macro isn't the way to go if you have five workbooks. I'd definitely agree if there are only five sheets. But if each book had, say, 20 sheets, you're talking about copy/pasting 100 sheets into one sheet. This can be done using VBA. But I'm neither an Excel nor a VBA guru. I can come up with an algorithm that I know is possible, though; I just don't know the precise code to do it.

Code:
Step 1: For each workbook in your list, open the file in an instance of Excel.
Step 2: Within the opened workbook, set the active sheet to the first sheet.
Step 3: Select all the active (dirty?) cells.
Step 4: Copy them into the new sheet.
Step 5: Loop through the sheets of the opened book.
Step 6: Close the book and open the next book.
Step 7: After all sheets of all books have been added to the new sheet,
        clean up the duplicates and reset formatting.
Something like this could be done pretty easily with a couple of nested For...Next loops. If I get bored today at work and nobody else does it I'll investigate a more specific solution.

HTH

chris.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Joined
Aug 5, 2005
Messages
3,086
Good caveat, Anne, I'm glad you pointed that out ([edit]that's why you're the mod! :) [/edit]). Most automated methods would either assume or require identical layouts between the copied sheet and the master. Otherwise you're begging for maintenance trouble. It's a good idea to normalize anyway if you want to catch duplicates.

So if your worksheets have different structures, you should probably consider redesigning them before merging them--especially if this is an ongoing application not a one-off need.

chris.
 

needsleep

Thread Starter
Joined
Sep 27, 2005
Messages
112
This looks good!! We'll let you know if it works -- it will be next week when we work on it. Thanks for your help up until now.
 

needsleep

Thread Starter
Joined
Sep 27, 2005
Messages
112
We found that we didn't know enough about the programming aspects to make this work for us. Further, the data sets were NOT able to be modified so all columns were the same. We ended up pawing through 2,000 records by hand. It took 26 hours. Thanks for your help, anyway.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top