+ Reply to Thread
Results 1 to 4 of 4

Remove Inexact Duplicates

  1. #1
    Registered User
    Join Date
    02-06-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    9

    Remove Inexact Duplicates

    Hello,

    I'm working on a spreadsheet that contains a lot of duplicate data. Forty-seven hundred rows of duplicate data, to be exact. It's a list of payors accepted by the hospital system I work for. The problem is that many have been entered several times with just a small change (for example, there is a "Selfpay" and a "Self Pay" and a "Self-Pay" and a "SELFPAY" etc - close to seventy variations just of Self Pay). I was able to use the Remove Duplicates to pare down the list from about sixty-five hundred rows of data, but it is still a lot of data to go through manually so I'm hoping there's a way to shorten it before I have to go through it myself. I've tried downloading the Fuzzy Lookup add-on, but as I don't have a second table to compare it to, I don't think that this tool is useful in this situation. (But please correct me if I am wrong!)

    The data is all contained in a single column (A) on a single sheet if that is needed information.

    Thanks for any and all assistance.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Remove Inexact Duplicates

    Hi Jill - You've described what varies, but we need to look for what stays the same, and you haven't given us anything to go on. If you attach a small sample workbook to your post, you'll likely get more responses. Take out any private or extraneous info, just show us where your data is and especially what form it takes. Thanks-Lee

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now just Browse for your file, then click Upload. Simple!
    Last edited by leelnich; 05-24-2017 at 06:40 PM.

  3. #3
    Registered User
    Join Date
    02-06-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    9

    Re: Remove Inexact Duplicates

    Hello,

    My apologies, I thought I had replied to this last week but it apparently did not post. Attached is the requested sample.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Remove Inexact Duplicates

    An easy option would be to create a mapping table in a separate sheet (one time activity - time consuming the very first time though) and run VLOOKUP. Every time you find a new derivative that is not there in the mapping table, keep adding them.

    See the attached file.
    Attached Files Attached Files

+ 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. Remove Duplicates in column and remove blanks
    By geliedee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 07:25 AM
  2. Remove duplicates function not removing duplicates
    By Berilium2 in forum Excel General
    Replies: 3
    Last Post: 04-01-2015, 06:55 AM
  3. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  4. Formula for detecting inexact duplicates?
    By Chucky2222 in forum Excel General
    Replies: 10
    Last Post: 06-21-2012, 08:53 PM
  5. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  6. Need VBA code to remove entries if there are duplicates (remove them totally)
    By BrandonFromSingapore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2012, 12:50 AM
  7. Replies: 2
    Last Post: 03-20-2011, 11:19 AM

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