+ Reply to Thread
Results 1 to 2 of 2

Looking to identify partial duplicates in Addresses (typos)

  1. #1
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Looking to identify partial duplicates in Addresses (typos)

    Hello,

    I have a sheet with customer ID and addresses, but many users over time have entered addresses in many ways... For example, some will enter "St." and some will enter "Street". I would like to automate this manual process of auditing these inconsistencies. Is there any way that anyone knows to write a formula or conditional formatting that would help point these out? I have attached a sheet as a reference. Thanks!!
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: Looking to identify partial duplicates in Addresses (typos)

    What's the goal here. Is it simply to remove any duplicates for a particular Customer Ref.
    If so why not use say the =LEN() function to identify the longest name since that's likely to be the preferred choice.

    In C2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then sort A:C by column A ascending and column C descending.
    Then in D2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Finally filter A:D for values >1 in column D, then select then filtered rows and delete them.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Identify Partial/Similar listings in an excel sheet.
    By vrachimis in forum Excel General
    Replies: 3
    Last Post: 05-27-2019, 12:34 PM
  2. Partial Match between addresses and highlighting
    By lborja in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2016, 05:03 PM
  3. identify and assign addresses
    By Akela928 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2015, 11:03 PM
  4. Replies: 2
    Last Post: 03-24-2013, 12:54 PM
  5. Replies: 0
    Last Post: 02-28-2013, 03:23 PM
  6. [SOLVED] Identify Duplicates and Non-Duplicates within an Array (Match)
    By simpson11 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2013, 09:49 AM
  7. Replies: 3
    Last Post: 03-09-2011, 07:00 PM

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