+ Reply to Thread
Results 1 to 13 of 13

Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Hello geniuses,

    I am a first time poster here but I've been spending some time with this issue and I'm not sure if it's possible. I've got 2 columns filled with text and what I'd like to do is compare A1 to B1:B5178 then A2 to B1:B5178 and so on down column A and flag those with a > 50% match by word (or maybe character if word isn't possible).

    I currently have a formula to flag exact matches, but I have way more "close" matches than exact.

    Any ideas?

    Thanks so much.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    what defines "50%"?

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Here is a simplified version of my sheet.

    I am already able to assess for exact matches, so I can determine that A23=B20 and A16=B19. What I am looking for is a way to flag the following as matches: A3 and B8, A7 and B7, A13 and B23. I chose "50%" as arbitrary amount of words to have in common.

    Is there any additional information I can provide that would be helpful?

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Hi jc19au,

    Try this:

    Please Login or Register  to view this content.
    it's genii isn't it????????
    Last edited by xladept; 11-07-2012 at 07:24 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Thanks xladept. It only changed the column header "Title B" to red, it didn't pick up the other "almost" similar matches. Hmmmm...

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    an alternative with formulas. create a helper column in C and use this, copied down...
    =LEFT(A2,LEN(A2)/2)
    then in D, use this, copied down...
    =IFERROR(VLOOKUP(LEFT(B2,LEN(B2)/2),$C$2:$C$23,1,FALSE),"")

    you could use the 2nd formula in CF to highlight the matches if you want

  7. #7
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Thanks, FDibbuns. It looks like it's catching some that are similar but not all. A key one is that I'd love if something could flag "minor head injury" as equal to "head injury - minor". I really appreciate you taking the time to look into this.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    That's strange - it worked for me.

    I tried it again and got four matches????????
    Last edited by xladept; 11-07-2012 at 08:33 PM.

  9. #9
    Registered User
    Join Date
    07-27-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007/2010
    Posts
    86

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    what about this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    EDIT: Doesn't work with words with few characters. Found too many matches. For example Mites.
    Last edited by bheanloh; 11-07-2012 at 08:15 PM.
    Boon

  10. #10
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    Thanks, bheanloh. I agree, it works for quite a few but not the shorter ones.

    Thanks for the help everyone! I think this sort of data is a bit complex and might require some manual attention.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    I am using /2 to take only 50% of the text. you could change the "2" to a cell reference with a % (say, F1) and then change both formulas to
    =LEFT(A2,LEN(A2)*$F$2)
    and
    =IFERROR(VLOOKUP(LEFT(B2,LEN(B2)*$F$2),$C$2:$C$23,1,FALSE),"")

  12. #12
    Registered User
    Join Date
    11-07-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    xladept - you're right! it's working really well for me this morning - sorry for the confusion. FDibbins, that change you suggested just above is also working quite well.

    Thanks so much everyone!

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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