Merging data from different worksheets

[Waterboy99]

Does anyone know how to merge data from different worksheets but weed out any duplicates.

For example, we download mailing list information from a couple of different sources to use in one mailing. The information format is basically the same. What we're doing now is copying the three worksheets together, sorting, and then manually deleting duplicate data.

It would be great, and way more time efficient, if we could merge the worksheets and have it not accept duplicate data.

Any thoughts?

[starguy]

Does anyone know how to merge data from different worksheets but weed out any duplicates.

For example, we download mailing list information from a couple of different sources to use in one mailing. The information format is basically the same. What we're doing now is copying the three worksheets together, sorting, and then manually deleting duplicate data.

It would be great, and way more time efficient, if we could merge the worksheets and have it not accept duplicate data.

Any thoughts?

I suppose you data is in the range of A6:A500

put in B7

=IF(ISERROR(MATCH(A7,$A$6:A6,0)),"",1) and copy this down to B500
(leave B6 blank)

it will show you 1 in col B if the address is duplicate.
select cells A5:B5 and apply Auto Filter (Data > Filter > AutoFilter)

select 1 from drop down list appearing in cell B5
(now it will show only those address which are duplicates)

select range in which those addresses are appearing and press Delete key.

select All from drop down list appearing in cell B5
now in column A some cells will be blank. to remove those blank cells
select range from A6 to last cell in with data in col A
press F5, click on Special tab and check Blanks then press Ok
it will select blank cells in col A.
right click on any cell selected and click Delete.
check Shift Cells Up and press Ok

you are done.

Regards.

[Waterboy99]

Thank you, starguy. I'll try it out and let you know how it works.

[Waterboy99]

That worked wonderfully! Thank you for your help!!!

[Kenom]

My setup is similair to this but different in a few ways.

Columns are setup as such:
A is first name
B is last name
C is physical addresss
D is city
E is State
F is Zip code.

Generally I will sort the data by column B and manually select any duplicates however my database has gotten rather large (2000+ rows) and we are constantly incorporating new addresses into our database. Obviously I want to be able to check when I merge a seperate mailing list to see if there are any duplicates and delete them based on last name, and address. (first name sometimes varies but still retains the same addresses)

I work for a Non-profit organization and we are constantly adding new donor lists to our database.

Any help is greatly appreciated. I'm using primarily excel 2003 but do have access to Excel 2007

[Waterboy99]

Why wouldn't starguy's solution work for you?

[Kenom]

well honestly I'm not that familiar with excel. Just the basics. The explanation does not make much sense to me as my B7 is populated. It's my assumption that since there is more than just one column populated that the technique above would not work. If that's not the case maybe you can explain exactly what it is that it is doing and how I can change it to work with my data.

[Waterboy99]

Just put it in another column ("G" for example). Change "A" references in the formula to whatever column you want to compare.