Deleting duplicates with a repeated address

**jadegreenwolf** · 10-26-2012, 11:09 AM

I have a list of 8000 clients and about 1/3 of them have duplicate addresses, but with different names. Is there a way to use the delete duplicate feature to delete the repeated addresses in that addresses column and at the same time delete the whole row that the duplicated address is on?

Thanks

Jade

**benishiryo** · 10-26-2012, 11:45 AM

hi Jade, welcome to the forum. The "Remove Duplicate" feature in Excel 2007 & above does do that. not sure if you're referring to the same one. go to Data-> Remove Duplicates. Check the option that "My data has headers". Check only the "Address" to indicate that it is only considered a duplicate when the address is duplicated.

**jadegreenwolf** · 10-26-2012, 11:52 AM

Hi, I tried that, but whats happening is that its only deleting a select few out of all the duplicate addresses when I choose to select all the headers. When I select the address column alone it only removes the duplicates from that column but the names stay in place instead of also being removed. What I need it to do is delete the duplicate addresses and delete that the entire row that the duplicate was on.

**jadegreenwolf** · 10-26-2012, 11:55 AM

so in other words, I need the rows with the same addresses but with different names deleted to only have the one name and one address.

I have a lot of duplicate addresses with different names. I just want to keep one name and one address.
I have over 7000 entries that looks like this:
_ A__________B___________C_______D____E______F
1
2 Last Name__First Name___Address__City__State__Zip
3 Smith______John________123 A____Det__MI_____48227
4 Smith______Joe_________123 A____Det__MI_____48227
etc.

Is there a script or a formula that I can use to help me out with this

**Alf** · 10-27-2012, 10:25 AM

Depending on how your database is set up perhaps a macro like this could do the job.

Please Login or Register  to view this content.

The macro just adds address, city, state and zip to a string and compares this with the row above also combined to a string. If the two rows (strings) match then
the last row gets deleted. So if your data is "groped" i.e. if 3 persons living at the same address are say on row 12, row 13 and row 14 the this macro will work.

If your data is not organized this way then you could use a helper row where you combine the values from row c, d, e, and sort you data and then use "deleting duplicates" as benishiryo suggested.

Alf