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

1. ## 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. ## 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:

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.
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.

3. ## 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.

4. ## 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????????

5. ## 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. ## 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. ## 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. ## 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????????

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

Formula:
`Please Login or Register  to view this content.`

EDIT: Doesn't work with words with few characters. Found too many matches. For example Mites.

10. ## 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. ## 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. ## 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. ## Re: Not sure if this is possible - compare 1 cell vs. entire column for a > 50% match

You're welcome!

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

#### 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