Combining Multiple Spreadsheets (semi-duplicates)

**gtramer** · 03-19-2011, 12:04 AM

Hi all,

I know it's been posted before but I can't seem to get my head around it, so I thought I'd start a new thread and show my working example file.

I have 3 worksheets in one .xls file and they are lists of restaurants. They have been scraped from different sources but there's approx 70% overlap. Problem is, from one source the relevant data might
a.) be slightly different from the other sources (so I need to define which is the default)
b.) one source might have more information (extra columns) than the other sources (perhaps including the default source).

How do I merge them?

I've attached a sample of what the working excel file looks like, and a sample of the outcome.
Assume the first worksheet is the default (if overlap, choose Data Capture 1 as right), then Data Capture 2, and lastly 3.

Hope you guys can help!

**Marcol** · 03-19-2011, 04:58 AM

Try this

Please Login or Register  to view this content.

This will copy all the sheets to a new sheet.
Duplicate entries are checked for empty cells and these are filled from the row below, if two rows have data in the same column, the first row takes precedence.
The "duplicate" row is then deleted
Finally the old worksheets are deleted.

If you need to keep the original data for any reason, including checking, use Save As to save the result, if not just save the workbook.

Try this on a copy of your real workbook first. To run the code use the macro "Main"

The code is not meant to be clever, I have tried to keep it as basic as possible, just one step up from the macro-recorder, that should help you follqw it easily and make any changes to suit your real sheet that you might need.

Hope this helps

**Marcol** · 03-19-2011, 11:22 AM

I said in my previous Post

The code is not meant to be clever, I have tried to keep it as basic as possible......

This is no excuse for bad practice

UsedRange is unreliable and should best be avoided

Change this Line (3 Places)

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

Thanks for the reminder Jerry

Apologies again

**gtramer** · 03-25-2011, 07:32 PM

Hey guys, it works well in theory but not so well in application. I'll attach the new data set to show you what I mean.

1. Entertainment Book's address field is the most accurate than the others but doesn't separate street number for street name.
2. Conoisseur Club and UrbanSpoon have extra fields like menu, logo, more Bio entries, meal price.
3. Entertainment Book has an Up To value for their discounts
4. I'd believe for a duplicate listing, Entertainment Book's phone number listing to be more right than the others and bio to be more right than the others.
5. In the event that the listings aren't spelt the exact same way, that's fine we can manually edit those if the other fields seem to be the same.
6. Entertainment Book's Type/Cuisine Type - Ideally these should have been merged to be the same field - either cuisine type or the others (retail, activity, travel).

I know it's a bit of work, happy to commission someone to help me get it perfect and clean.