Combine DeDupe address data from several sheets

[LesleyB]

Hi

This is a problem I regularly have to deal with and have always done manually, but I am sure you clever guys can help;-)


Client provides me with 2 or 3 (or more) spreadsheets with name, address, contact info... could be up to 30 columns or more.... but the column layouts on the different sheets don't match. For example, one sheet might be data exported from Outlook, another might be downloaded from their email newsletter list, so they have different column names and in different places on their respective sheets (eg the email address might be in column 5 "email" on one sheet and column 10 "main email" on another).


So Two steps:

STEP1:

I need all the data in a single sheet with all the columns matched up, then

STEP 2:

to be able to dedupe in different ways:


1. REMOVE entries where ALL data matches in every column (I can do that with the Remove Duplicates Button)

2. MOVE entries where SOME of the data matches (based on specified columns)- so that I can then manually check which is the correct data (for example there may be the same person with two different addresses - it might be that on is now outdated and I can delete it, or it could be that one is home and one is business & I need to capture both, using additional columns). Ideally, both versions of the record would be moved together, so that I can easily see what info is duplicated and what info is different.


Then I need to get all the checked & finished entries back together in one sheet with the unique ones (which I could just copy & paste)


I am sure there must be a tool or plug in out there that I can use, or someone can help me with a macro or VBA code!


Many thanks in advance for any help you can give!

 

[NARAYANK991]

Hi Lesley ,


I think before you can run a macro which will do step 1 , you need to specify to the macro which columns actually match up ; for instance , you say that the same information , such as the email address , can be in different columns , which have different column headers , in the two workbooks or worksheets ; how is the macro supposed to deduce that these two columns are to be matched ?


You also say that there is no way to match using the information itself , since one email address can be a personal one , while the other can be the official one.


I think if you can have a table where you specify which column from one workbook / worksheet is to be matched with which column from the other workbook / worksheet , then the macro will be simple to write.


Narayan