Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts

    Count same characters at beginning of string

    Hi. I'm a first time user. I've made a couple of changes to the psot to see if I can get another option.

    I have a huge list of names to check if they have duplicates.

    The problems is that sometimes the same name is spelled differently for the same person in the list, or sometimes a middle name has been added, or, as it happens in latin countries, both parents' last names are used..

    So, I'd like to check the first characters in a string in a cell to see how many are the same.

    For example, here's a made-up list:

    A1 John Doe
    A2 John Doe Martinez
    A3 John Dowe
    A4 John Dove


    Is there an Excel function I can use that will tell me how many characters are the same from the cell above it?

    So, in the example above the cells where the formula would be, would display:

    A1 John Doe B1 0 (or "NA" or "REF" because there's no cell to compare to above it)
    A2 John Doe Martinez B2 8
    A3 John Dowe B3 7
    A4 John Dove B4 7

    Because A2 shares 8 characters in a row with A1, A3 shares 7 characters with A2, and A4 shares 7 characters in a row with A3, if you include the spaces. So the Column B cells show the number of characters the cell in the A Column shares with the cell above it... I hope this is easy to understand.

    I tried the Soundex solution below, but when I apply it I get a code which although helpful, does not help me as much as some code or a formula like the one I am describing.
    Thanks!
    Last edited by olives; 2015-04-06 at 15:06.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Olives

    Welcome to the Lounge!

    It seems to me you would be better off investigating the use of a SOUNDEX function.
    Try here:

    http://j-walk.com/ss/excel/tips/tip77.htm

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Olives,

    Zeddy in a previous post provided the sample workbook that demonstrates the Soundex function.

    http://windowssecrets.com/forums/sho...hlight=soundex

    Worth taking a look.

    Maud
    Last edited by Maudibe; 2015-03-31 at 21:53.

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    olives (2015-04-07)

  6. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    There was also a thread here within about the last month (can't find it now) where someone pointed to a MS download that does fuzzy matching. I downloaded this but haven't installed it yet. From my recollection (perhaps faulty), this also might address the question.

    Fred

  7. The Following User Says Thank You to fburg For This Useful Post:

    olives (2015-04-07)

  8. #5
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Welcome to the Lounge!...
    Thanks, zeddy! I'll take myself there and see what I can learn!

  9. #6
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    ... zeddy... provided the sample workbook that demonstrates the Soundex function...
    Much obliged, Maud, I'll check iut tonight. Apprecaite the response!

  10. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by fburg View Post
    ... someone pointed to a MS download that does fuzzy matching...
    I found it, but it requires downloading and installing VSOT so eventually I'll get around to it. Thanks!

  11. #8
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    I re-edited the OP to see if I could get some more help with this problem.

  12. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Are you only looking at the row immediately above?

    Is the attached close?
    Attached Files Attached Files

  13. The Following User Says Thank You to kweaver For This Useful Post:

    olives (2015-04-07)

  14. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Olives

    I tried the Soundex solution below, but when I apply it I get a code which although helpful, does not help me as much as some code or a formula like the one I am describing.
    I assume that, after entering the soundex function formulas, you then sorted the data using the column of soundex values as your sort key???? The whole point of using soundex is to get the entries that 'sound alike' together, and you have to sort them to do that.

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  16. #11
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    ... I assume... you... sorted the data...
    I did, zeddy. It helped out, but, since I have each name in one cell, meaning first name, middle and last and maternal last name, if given, in the same cell, I was getting different codes for names that I wanted much closer together... Thinking about it, maybe I should have separated ecah first name and last name for better use, but the amount of work dissuaded me... I like the ficuntion, though and am keeping it in my toolbox... I researched its history and have already shared it woth some freinds doing the same work, so I have to thank you anyways... Here's what I mean about the codes, with my examples above:

    A1 John Doe J530
    A2 John Dowe J530
    A3 John Dove J531
    A4 John Doe Martinez J535

    When I'm looking at a list of over a thousand names, I would have wanted A1 and A4 closer together, and that's why I wanted to see if there was something else I could try... But I can't thank you enough...

  17. #12
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    ... Is the attached close?
    That's beautiful! That's exactly what I wanted! I know I'll still have some diffficulties based on maternal last names, but this is exactly what I had in mind! Thank you!

  18. #13
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Thank you all! I think I can use a combination of both methods now and am really grateful for the help! A virtual beer to each of you!

Posting Permissions

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