Solved

Compare Similar Cells - Excel

Posted on 2016-09-22
11
35 Views
Last Modified: 2016-10-29
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
0
Comment
Question by:GulfIT
  • 6
  • 2
  • 2
11 Comments
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 41810493
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41810495
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
 

Author Comment

by:GulfIT
ID: 41810617
Thanks,

Can i use the same to check for duplicates in a column matching to a certain percent ?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41810845
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
 

Author Comment

by:GulfIT
ID: 41814315
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 41835576
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41835780
Shahid
You keep referring to UDFs, if you have a working file, why don't you share it with OP?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41836605
@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
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points (awarded by participants)
ID: 41836956
@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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41865043
.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now