Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Compare Similar Cells - Excel

Posted on 2016-09-22
11
Medium Priority
?
55 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 27

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 27

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 27

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 27

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 27

Accepted Solution

by:
ProfessorJimJam earned 2000 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 27

Expert Comment

by:ProfessorJimJam
ID: 41865043
.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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