CODEWITHSUNDEEP
excelexcel-formulafuzzy-searchvba
DeerSpotter
DeerSpotter

Reputation: 433

How can I do a keyword match from the content I am searching in Excel?

Here is the Data that i am trying to analyze:

enter image description here

As Shown in the picture, i am trying to match Column H with Column I. Any keyword match from Column H that matches in Column I would return a "1" and if no keyword matches it would return a "0".

I tried using Fuzzy Lookup, but am getting frustrated that it keeps giving me percentages and not a single 1 and 0. (true or false).

Is there anyway to do this with either a macro or equation or if someone could help me with Fuzzy Lookup too if that is a good solution as well.

Any help that could guide me in the right direction will help a lot. Thanks!

Edit: (10/27/17)

UPDATE:

I have used this users answer here: https://superuser.com/a/984389/464791

It is really useful. But it still lacks what i believe every single excel user/dev needs, which is a string comparison in the way i describe above. I am still looking for a solution mainly so i can learn and that others in the future could have one. As of right now i don't need this solution anymore. But it is still something i would like to work on.

Upvotes: 0

Views: 189

Answers (2)

stefan
stefan

Reputation: 239

Try to use this code in a module... Use the variables to change the Worksheet name, the scanning location, the starting row and so on.

I tried to set most of them as you need it.

It just splits every string at their spaces maybe that can help you.

Option Explicit
Dim ws As Worksheet
Dim col1 As Integer
Dim col2 As Integer
Dim colFlag As Integer
Dim rowStart As Integer
Dim urWs1 As Long
Dim scanArr As Variant
Dim actScan As Variant
Dim i As Long

Sub test()
    'Worksheet
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    'Column D = 4
    colFlag = 4
    'Column H=8
    col1 = 8
    'Column I=9
    col2 = 9
    'Start scanning
    rowStart = 2
    'Count Rows
    urWs1 = ws.UsedRange.Rows.Count

    For i = rowStart To urWs1
        ws.Cells(i, colFlag).Value = 0
        'Split at spaces
        scanArr = Split(ws.Cells(i, col1).Value, " ")
        For Each actScan In scanArr
            If InStr(1, UCase(ws.Cells(i, col2).Value), UCase(actScan)) <> 0 Then
                ws.Cells(i, colFlag).Value = 1
            End If
        Next actScan
        'Opposite direction
        scanArr = Split(ws.Cells(i, col2).Value, " ")
        For Each actScan In scanArr
            If InStr(1, UCase(ws.Cells(i, col1).Value), UCase(actScan)) <> 0 Then
                ws.Cells(i, colFlag).Value = 1
            End If
        Next actScan
        scanArr = ""
    Next i


End Sub

/edit: uppercase comparison

Upvotes: 1

Maldred
Maldred

Reputation: 1104

Fuzzy Lookup is intended to return a percentile to reference how close of a match it is. Please refer here

One option you could do is us the =IF() and have it display a 1 or 0 depending on how close of a match up it is.

ie. =IF( D1 >= .50 , 1 , 0) or =IF( D1 <= .50 , 0 , 1)

Upvotes: 1

Related Questions