+ Reply to Thread
Results 1 to 5 of 5

Flagging numerous addresses

  1. #1
    Registered User
    Join Date
    12-15-2009
    Location
    Gloucestershire
    MS-Off Ver
    Excel 2000 + 2007
    Posts
    3

    Flagging numerous addresses

    Hi

    I have a document containing thousands of addresses. Many of the addresses are for the same location(although some are written differently) I need to be able to flag any addresses that appear 3 or more times. Is there an easy macro that i can write to do this?

    Hope you can help.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: 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

  3. #3
    Registered User
    Join Date
    12-15-2009
    Location
    Gloucestershire
    MS-Off Ver
    Excel 2000 + 2007
    Posts
    3

    Re: Flagging numerous addresses

    Hope this sample is ok

    Cheers
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Flagging numerous addresses

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-15-2009
    Location
    Gloucestershire
    MS-Off Ver
    Excel 2000 + 2007
    Posts
    3

    Re: Flagging numerous addresses

    Teylyn

    That is great. Many thanks. I presume that if want properties that appear more than 5 times i just change the >2 at the end of the formula to >4

    Again many thanks for your help

    aviner1974

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1