Solved

Compare Similar Cells - Excel

Posted on 2016-09-22
11
38 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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