+ Reply to Thread
Results 1 to 6 of 6

When comparing names from two columns, is there a way to "clean" up the data?

  1. 03-26-2024, 11:41 AM #1
    Surferdude949
    Surferdude949 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    15

    When comparing names from two columns, is there a way to "clean" up the data?

    I'm comparing two lists of user names from separate sources and each came in a separate workbook with multiple tabs. I combined the user names list into a new workbook by copy n pasting. I noticed there were some spacing and misspelling discrepancies. Is there a way for Excel to automatically check for spacing and name spelling?

    See discrepancies below

    2024-03-26 08_34_39-Book1 - Excel.jpg

    In addition, to compare the two rows I tried using a Vlook up function but there were some errors where a name was duplicated on row a and b but the function did not find them. I resorted to using the Conditional formatting, Highlight cell rules, duplicate values.
    Reply With QuoteRegister To Reply
  2. 03-26-2024, 02:25 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: When comparing names from two columns, is there a way to "clean" up the data?

    This is a massively difficult problem. There are commercial products that do this kind of cleanup at great expense.

    Part of the problem is

    William Jerry
    William Jery

    How do you know whether one is a misspelling, or whether this is two different people with similar names?

    You might want to look into Microsoft's Fuzzy Lookup add-in.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 03-26-2024, 05:44 PM #3
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: When comparing names from two columns, is there a way to "clean" up the data?

    A video about Fuzzy Matching in Power Query.

    Power Query Fuzzy Matching Makes Lookups EASY!
    Reply With QuoteRegister To Reply
  4. 03-26-2024, 11:30 PM #4
    Surferdude949
    Surferdude949 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    15

    Re: When comparing names from two columns, is there a way to "clean" up the data?

    Quote Originally Posted by 6StringJazzer View Post
    You might want to look into Microsoft's Fuzzy Lookup add-in.
    Ok np. I'll eye ball it, thx.
    Last edited by 6StringJazzer; 03-27-2024 at 07:35 AM. Reason: Please do not quote entire posts
    Reply With QuoteRegister To Reply
  5. 03-26-2024, 11:46 PM #5
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: When comparing names from two columns, is there a way to "clean" up the data?

    Tks for the feedback, glad to have helped.
    Reply With QuoteRegister To Reply
  6. 03-27-2024, 11:33 AM #6
    CarissaKirby
    CarissaKirby is offline
    Banned User!
    Join Date
    03-11-2024
    Location
    usa
    MS-Off Ver
    CURRENT
    Posts
    23

    Re: When comparing names from two columns, is there a way to "clean" up the data?

    Using the Microsoft Fuzzy Lookup add-in in Excel, you can compare "William Jerry" and "William Gerry", taking into account possible typos and similar sounds. The program will show possible matches, which will help determine whether they are the same person or different people.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to count "low", "med", or "high" in 4 columns of data split by fiscal quarter
    By Kerry1980 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-11-2022, 06:58 AM
  2. [SOLVED] "Defined Names" into "Data Validation" problem
    By JimmyWilliams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2020, 11:44 AM
  3. How to clean huge data to get "top five" country by the range of year
    By dibyendu2280 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-23-2019, 11:23 PM
  4. [SOLVED] Code Clean-Up: Delete "False" worksheet created when "Cancel" is chosen in Input Box
    By Kenny Blackwell in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 08-31-2015, 10:00 AM
  5. Comparing data between 2 worksheets, mark them as "yes' or "no" based on several condition
    By pyol17 in forum Excel General
    Replies: 3
    Last Post: 08-28-2012, 10:49 AM
  6. [SOLVED] Utility to "clean up" or "defrag" large Excel file
    By Sabrina in forum Excel General
    Replies: 3
    Last Post: 01-12-2006, 06:00 PM
  7. [SOLVED] "Clean Me" Macro is giving "#VALUE!" error in the Notes field.
    By Ryan Watkins in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 08:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1