+ Reply to Thread
Results 1 to 4 of 4

Match comparing characters not the cell value

  1. #1
    Registered User
    Join Date
    07-26-2010
    Location
    NL
    MS-Off Ver
    Excel 2016
    Posts
    70

    Match comparing characters not the cell value

    Hello,

    I am looking for a way to compare cells with each other.
    Often there is a space, punctuation mark or typo in the cell. therefore vloolup , match e.g. ends up in a #N/B.

    Is there a way, maybe with a loop in VBA, to find the cell which contains the most comparing characters.
    Got a list with names that are correct and a bigger list with available users.

    Examples:
    "Arjan Busger op Vollenbroek" (from list of names) does not match "Arjan Busger opollenbroek" (from list of users) therefore resolves in #N/B but has 25 the same characters and therefore matches 93%
    "John Doe" (from list of names)does not match "John W. Doe" (from list of users) but has 8 matching characters and therefore matches 73%.

    So for every name in the list I need to find the cell (or cell value) with the most comparing characters, the last check can be done visualy by hand, to avoid anagrams.

    Anyone an idea?

    Best Regards,

    Arjan B. Busger op Vollenbroek
    a.ka. ABBOV

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,462

    Re: Match comparing characters not the cell value

    Hi.

    I think you need to rethink your definition of "matching characters".

    For example, if we were to simply compare the number of occurrences of each character within one string and the number of occurrences of those same characters within another string, then, for:

    "John Doe"

    and:

    "Eod Nhoj"

    we would obtain a result of 100%.

    Surely position (or relative position) needs to be taken into account also? However, since this is your post I'll leave it to you to further refine your definition.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-26-2010
    Location
    NL
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: Match comparing characters not the cell value

    Quote Originally Posted by XOR LX View Post
    Hi.

    I think you need to rethink your definition of "matching characters".

    For example, if we were to simply compare the number of occurrences of each character within one string and the number of occurrences of those same characters within another string, then, for:

    "John Doe"

    and:

    "Eod Nhoj"

    we would obtain a result of 100%.

    Surely position (or relative position) needs to be taken into account also? However, since this is your post I'll leave it to you to further refine your definition.

    Regards
    Hello Xor,

    Thank for your quick answer, the (relative) position would sure be nice, but makes it more complicated.
    As mentioned in the original post, "the last check can be done visualy by hand, to avoid anagrams."

    Best Regards

    ABBOV

    This would help me sort out a list

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,462

    Re: Match comparing characters not the cell value

    Ok. You've posted in the Formulas & Functions subforum, not the VBA one. Would you therefore prefer a non-VBA solution if possible (which it is, though perhaps not as efficient)?

    Regards

+ 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. Replies: 0
    Last Post: 02-11-2014, 11:28 AM
  2. Match method always returns false (comparing cell's value to a list)
    By Phalanx_ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2013, 04:09 AM
  3. Comparing characters
    By GaidenFocus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2013, 12:30 PM
  4. [SOLVED] Comparing two excel files to fill a cell if there is a match
    By greenmat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2012, 06:05 PM
  5. Replies: 2
    Last Post: 11-01-2011, 05:14 PM
  6. Replies: 3
    Last Post: 10-16-2011, 02:46 AM
  7. Replies: 4
    Last Post: 07-26-2011, 08:22 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