Compare Similar Cells - Excel

Hi,

I have a requirement to compare a list of values via vlookup but are a not exactly equal. I guess as a most of that characters are similar where 2 or 3 characters can differ to each other.

Is there a function in excel i can used to compare values that are 80% similar to each other?

Thanks,
GF
GulfITAsked:
Who is Participating?
 
ProfessorJimJamCommented:
@GulfIT

and if you do not want to use commercial add-in, then option C) in my post ID: 41810495

attached is workbook with embedded UDF

it is free UDF and original author is Allan from Mrexcel.

combined formula & UDF i used
=IF(FuzzyPercent(A2,FuzzyVLookup(A2,$H$2:$H$10,1,,,,3),3)>0.6,"Duplicate Found in List2", "Not Duplicate")

Open in new window

EE.xlsm
0
 
Shahid ThaikaSole ProprietorCommented:
Google FuzzyVLookUp. You can download the code as a VBA Function script. Basically, you add a macro to your file and copy-paste the given code. It works likes VLookUp, and its cousins, but allows for partial match.
0
 
ProfessorJimJamCommented:
there are couple of options

A) using Microsoft Free Fuzzy Lookup Add-In    this is good but not user friendly

B) a commerial add-in that have a function called Fuzzy vlookup    this is good but not free, however there is a free trial period. not expensive though.

C) a free UDF written by Allan in Mrexcel forum. there are many versions of them, but the working version, i have somewhere in my archives. if the option A and B did not work for you then i will try to find the UDF and post it here.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
GulfITAuthor Commented:
Thanks,

Can i use the same to check for duplicates in a column matching to a certain percent ?
0
 
ProfessorJimJamCommented:
you are welcome.  

yes you can use the commerial add-in to check for duplicates it has another feature in the add-in where you can use duplicate finder on fuzzy basis.
0
 
GulfITAuthor Commented:
Cannot see any duplicate detection options in the PowerUPS tab, in the Commercial add-in, What function should i use to identify partial duplicates.
0
 
Shahid ThaikaSole ProprietorCommented:
FuzzyVLookup has a FuzzyPercent formula. Pass your original string and your found duplicate string and it will give you a match function. If your intent is to only match above a certain percentage, you can mention this as an input in the FuzzyVLookup formula.
0
 
ProfessorJimJamCommented:
Shahid
You keep referring to UDFs, if you have a working file, why don't you share it with OP?
0
 
ProfessorJimJamCommented:
@GulfIT

you should use the pwrMatch function of the commercial add-in to be able to detect duplicates.

please see attached example.

i used the pwrmatch to find the duplicates of list1 in list2.

please see attached file.   if you have installed the commerial add-in trial version, the attached workbook will show and will work the result.

2016-10-10-11_44_15-Microsoft-Excel-.pngEE.xlsm
0
 
ProfessorJimJamCommented:
.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.