0

As the title suggests I have a currently working piece of code that I would like to adjust to make it have more of a fuzzy match feature.

Sub FillInBlanks()
Application.ScreenUpdating = False
  Dim rng As Range
  Dim rng2 As Range
  Set rng = ActiveSheet.Range("C2:C9452")
  Set rng2 = ActiveSheet.Range("D2:D9452")

  For Each cell In rng
    If IsEmpty(cell) Then
        cell.FormulaR1C1 = "=VLOOKUP(Department!RC[-1],'All Titles'!C:C[1],2,)"
        Sheets("Department").Columns(3).Copy
        Sheets("Department").Columns(3).PasteSpecial xlPasteValues
    End If
  Next cell


  For Each cell In rng2
    If IsEmpty(cell) Then
        cell.FormulaR1C1 = "=VLOOKUP(RC[-1],'All Titles'!C[-3]:C[-2],2,)"
        Sheets("Department").Columns(4).Copy
        Sheets("Department").Columns(4).PasteSpecial xlPasteValues
    End If
  Next cell
  Range("B2").Select


Application.ScreenUpdating = True
End Sub

As you can see it does a vlookup to find a match. My problem is that it can only do exact matches with the other range. I would like to make it match based on a certain amount of likeness to the other data set. This eliminates countless issues with the minute differences in data. I'm aware this sounds excessively complicated and it may be out of my ability to implement but I figured it's worth a shot. I can't teach all off my colleagues how to use it so if I can keep it as just a click of a button that would be great.

Medicated Gorilla
  • 21
  • 6
  • [This answer](http://stackoverflow.com/a/4243652/4088852) might be of use. Just set a delta for it and use that in your reimplementation of VLOOKUP. – Comintern Dec 01 '16 at 22:30
  • @Comintern Thanks! Most of this is over my head as of now. I sort of get how it works but not really. I can't see how exactly to implement it and have it still accomplish the vlookup. – Medicated Gorilla Dec 01 '16 at 22:47
  • You'll need to knock together your own version of vlookup. That's the easy part - there are tons of examples for finding values in a column kicking around here. – Comintern Dec 01 '16 at 22:50
  • 1
    I believe you can use the wildcard in `VlookUp()`, `*`, would that help? Also, if it's a feature limited by `VlookUp()`, there's also [`Index/Match`](https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/) which may be helpful, as `match()` can help find [approximate matches](https://exceljet.net/lessons/how-to-use-match-to-find-approximate-matches). – BruceWayne Dec 01 '16 at 23:02
  • Microsoft has a fuzzy-lookup add-in available for free download. Here is a YouTube video explaining how to use it: https://youtu.be/3v-qxcjZbyo – John Coleman Dec 02 '16 at 00:32
  • @BruceWayne Thanks! I'm working on it now – Medicated Gorilla Dec 02 '16 at 16:26
  • @JohnColeman It is available but its somewhat limited unfortunately – Medicated Gorilla Dec 02 '16 at 16:26

0 Answers0