Hello Guys,
I have always used Vlookup and returned the exact value but this time i am struck.
I am in a situation where in i would like to lookup value in Column A Sheet 1 in Column A in Sheet 2 and return the percentage match value
for example if Column A sheet 1 has
Apple
Pear
Column A Sheet 2 has
Aple
Pear
I would like the results to be some thing like in Column B Sheet 1
Apple 99%
Pear 100%
Last edited by ismailshajji; 11-16-2020 at 06:55 PM.
ummmm.....i think you gave use the wrong data in col a, sheet 2. were those supposed to be %ages?
thank you so much for taking you time.Originally Posted by vba_php
i have attached original data to my original post.
I am trying to check if the cell values of sheet 1 are present in column 1 of sheet 3 using vlookup and if it is present then i would like to return the percentage match as the return value.
i was looking at the forumn and came across this Levenshtein but not sure how to implement it .
what do you mean percentage match? that has nothing to do with the cols of data you provided. the only thing one can do is use vlookup to see if data exists in one col based on the other col. or are you trying to detect whether strings are similar? as in: one string is inside of the other somewhere? that's the only thing I can think of that you can possibly mean.
see attached workbook for all that can be given to you at this point, given the info that you posted. is the APPROX vlookup() match col in the book what you're looking for?
How do you want the percentage match calculated (provide a manually calculated example)?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I was thinking like vba_php that it looks like you want some kind of string similarity algorithm. Levenshtein distance is one such algorithm (my internet search pulled this up: https://itnext.io/string-similarity-...gi=b4d06d1be3a ).
To my knowledge, Excel does not have any of these "string similarity" algorithms pre-programmed in. A quick internet search for "levenshtein distance excel" found some interesting possibilities:
A stackoverflow q&a that has a javascript(?) udf (for use in google sheets, though I understand that the newest versions of Excel are transitioning to using javascript as a macro language, so it might be useful in that context). It also has a google sheets worksheet formula only approach that should be easily downloaded into Excel: https://stackoverflow.com/questions/...g-to-resort-to
From our forum, this sample sheet includes a VBA UDF that claims to use a weighted Damerau-Levenshtein algorithm: https://www.excelforum.com/excel-gen...ml#post3431918
A VBA module on github: https://gist.github.com/draffensperger/7176944
Assuming it is string similarity you are trying to do and Levenshtein is an acceptable algorithm, see if any of that helps.
not sure if anything that advanced will be that well received, Shorty. hmmmmmm
Brother thank you so much for your help. I work in hospital and have been really busy and haven't had a chance to visit the forum.
Thank you for attaching the test data file with the formulas, I can see that the approximate match is actually getting the closer results but in the row below.
What i am trying to do is i have a data of Equipment models in sheet 1 that i would like to see if they are present in sheet 2, exact match can find any thing that is true but there are some models that are codes with different spellings and i would like to count all the alphabets in sheet 1 Cell and compare the data on sheet 2 and get a percentage anything that is 100% is exact match anything that is over 95 % should be ok and i should be able to correct them manually.
I hope the above explanation is ok, but if its bit difficult its ok i will manually check them and correct it .
Regards,
you can't get that with any excel functions. you will have to write code. and that in itself will be overly complicated. sorry.
Oh ok thank you.
Did either of you have a look at the spreadsheet that I mentioned in this link? https://stackoverflow.com/questions/...g-to-resort-toyou can't get that with any excel functions. you will have to write code.
It seems to manage a Levenshtein distance algorithm for up to 15 character strings without any code. Expanding to longer strings should only require some strategic copy/paste to expand the matrix to whatever size you need (up to the spreadsheet's limit of 16000 columns). Assuming that Levenshtein distance is an adequate measure of string similarity for the OP, it seems to accomplish the task.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks