Thread: Flagging numerous addresses

Hi,

identifying duplicates is not so hard to do. But

same location(although some are written differently)

might be a bit more difficult. You should post a workbook with some sample data, especially duplicates that are "written differently" and specify the rules by which you would expect Excel to identify them as duplicates.

To post a workbook, click "Go Advanced" below the Quick Reply box and then the paper clip icon

cheers

OK,

you may need to involve a helper column. Insert a column after B and put this formula in the new cell C1:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,","," "),";",""),"_"," "))

Copy down the length of your list.

Next, create a conditional format for Column A. Highlight all values in Column A and then go Format - Conditional formatting - Formula is

=COUNTIF(C:C,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,","," "),";",""),"_"," ")))>2

and select a format.

You can hide the newly inserted column if you wish.

note: The substitute formula will catch commas vs underscores and will eliminate semicolons, but it will not adjust things like

77, LINDEN AVENUE_prestbury,
77;,LINDEN AVENUE NO 77_PRESTBURY,

so a certain element of visual checking will remain. The conditional formatting in the attached could be replaced with a flag in the helper column, if you want.

see attached.

hth