?
Solved

Compare Similar Cells - Excel

Posted on 2016-09-22
11
Medium Priority
?
46 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 26

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 26

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 26

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 26

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 26

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 26

Expert Comment

by:ProfessorJimJam
ID: 41865043
.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

741 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