Duplicates that aren't exact.

Posted by Cyndi on January 02, 2001 2:27 PM

Hi I'm new at Excel. I have a huge list with mailing address that were entered by a number of sources and are not exact how and I find and take out the duplicates.
For example the list might have in column A-F
Alamogordo Public Schools Group Travel Partner 103 Cuba Alamogordo NM 88310

And then below that in A-F

Alamogordo Public Schools Group Travel Partner 103 Cuba Ave Alamogordo NM 88310

The only difference being the st. address in column C or it could be the name of the school that is off in column A.

Is there anything I can do?

Please Help. Thank you so much.

Cyndi

Posted by Aladin Akyurek on January 02, 2001 6:36 PM

Alamogordo Public Schools Group Travel Partner 103 Cuba Ave Alamogordo NM 88310

I'll assume your first record is in A2 thru F2 and the last cell of data, say, F43. And, this is important, type in G3 (not G2)

=IF(ISNA(MATCH(D2&E2&F2,D3:$D$43&E3:$E$43&F3:$F$43,0)),"","Row "&MATCH(D2&E2&F2,D3:$D$43&E3:$E$43&F3:$F$43,0)+ROW(F2)&" is possibly a DUP of row "&ROW(F2))

This is an array formula. To enter it hit control+shift+enter. Copy this formula down as far as needed.

I'm using as lookup-value a combination of City, State, and Zip. You might try also other fields (in H3).

Hope you catch at least a number of dups with this.

Aladin