Solved

Find something that is not an exact match

Posted on 2016-10-26
23
53 Views
Last Modified: 2016-11-02
Is there a formula that will find items even though they are not exact. I tried using a variation of VLookup but failed miserably.

Lets say I have these on Sheet 1

272637 27FEB600304

SECURE TRUST B1016003 BS BMCE2


And these on Sheet2

SECURE TRUST B1016003

272637 27FEB600304 BS BMFSP  (2)

I am sure there is a way but only an expert will know the answer to that.

Here’s hoping
0
Comment
Question by:Jagwarman
  • 10
  • 7
  • 3
  • +2
23 Comments
 
LVL 8

Expert Comment

by:Koen
ID: 41859896
Microsoft offers an add-in for excel that can do 'fuzzy' lookups...

you can find it here
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41859903
Vlookup could work if you don't use the TRUE;FALSE option/argument at the end (which is optional)
0
 
LVL 8

Expert Comment

by:Koen
ID: 41860100
Vlookup without true; false is dangerous when comparing strings.. you need your data to be sorted...so you depend on the classicall alphabetical sort, which with 'fuzzy' strings could result in two near equal strings to be miles apart.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41862231
When there is a 'match', are the strings going to be identical in their leading (left-most) characters?
0
 

Author Comment

by:Jagwarman
ID: 41862413
Hi aikimark, yes they are
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41862558
A function could easily do this, but you seem to have your heart set on a formula.

I was thinking there might be some way to combine the Left() and Len() functions, but I don't know if the Left() function can be used that way
0
 

Author Comment

by:Jagwarman
ID: 41862581
I would be happy to use a function aikmark
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41862633
This will return the row in the lookuprange where the left-side-match is found.  A zero value is returned
Function Q_28978929(ByVal parmText As String, parmLookupRange As Range) As Long
    Dim rng As Range
    If Len(parmText) = 0 Then
        Exit Function
    End If
    For Each rng In parmLookupRange
        If Left(rng.Value, Len(parmText)) = parmText Then
            Q_28978929 = rng.Row - parmLookupRange.Rows(1).Row + 1
            Exit Function
        End If
    Next
End Function

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41862784
Ah now you've got me aikmark. I am obviously familiar with Sub but not function. How do I get that to run? Sorry for being so dumb
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41862828
1. put it into a module in your workbook
2. create a formula in your worksheet that references a single cell (first parameter) and a range (second parameter)
0
 

Author Comment

by:Jagwarman
ID: 41862857
I am dumber than I thought I was :-(
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Jagwarman
ID: 41867975
aikimark, sorry but I have tried to work this out, reading up on google how to kick start a function but cannot work it out.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41868092
@Jagwarman

please open attached file. i have put the fuzzylook UDF originally written by Alan from MRExcel

you can see that in sheet1 column B the matched value is returned from Sheet2
EE.xlsm
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41868170
@Jagwarman

1. Do you have a module in your VB project?
If not, Alt+F11 to get you into the VB environment.  Insert a new module.  Paste my function

2. What does your formula look like?
0
 

Author Comment

by:Jagwarman
ID: 41868474
aikimark I am using this formula

=RIGHT(CELL("filename",D2),LEN(CELL("filename",D2))-FIND("]",CELL("filename",D2)))
0
 

Author Comment

by:Jagwarman
ID: 41868503
also tried =IF(L1<=1,SUM(L1:M1))
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41868659
I don't see any reference to the Q_28978929 function.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41868667
0
 

Author Comment

by:Jagwarman
ID: 41868951
aikimark I'm sorry I don't know how to do that
0
 

Author Comment

by:Jagwarman
ID: 41868954
ProfessorJimJam I tried that it kinda works but I have 12000 lines and so many are invalid matches.
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 41869009
@Jagwarman

it did not work because we did not put the % of strength.  now i set that to 75% but putting .75 into the next argument of UDF.

you can increase it, but i am sure this will work.

please see attached.
EE.xlsm
0
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 41869064
Look at the formulas in the attached workbook
Q_28978929.xls
0
 

Author Closing Comment

by:Jagwarman
ID: 41869678
both of these are going to be able to help me so many thanks and thank you for sticking with me.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 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

23 Experts available now in Live!

Get 1:1 Help Now