Results 1 to 5 of 5

Thread: Find a Partial Match from Location Sheet

  1. #1
    VBAX Regular
    Feb 2012

    Find a Partial Match from Location Sheet

    Hi Support Team,

    I've an excel sheet(Data Sheet) with some device names. The device name will consist of a code. The codes may be either placed anywhere on the string. The codes are listed in the Location sheet. First, i want to find out the location code from the device name based on the matches from Location sheet. Once we find the location code, i can map the location name of Region

    Can some one help with a script that can find out the code that is within the device name based on location sheet.

    Sample Sheet is Attached. I've highlighted the code in Device name inorder to understand an example.

    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    here is another way to do it.
    Attached Files Attached Files

  3. #3
    Try so.
    Sub Maybe_So()
    Dim i As Long, j As Long
    Dim shDat As Worksheet, shLoc As Worksheet
    Dim locArr
    Set shDat = Worksheets("Data")
    Set shLoc = Worksheets("Location")
    locArr = shLoc.Cells(1).CurrentRegion.Value
    datArr = shDat.Cells(1).CurrentRegion.Value
    Application.ScreenUpdating = False
        For i = 2 To UBound(datArr)
            For j = LBound(locArr) To UBound(locArr)
                If InStr(datArr(i, 1), locArr(j, 1)) <> 0 Then
                    datArr(i, 2) = locArr(j, 1)
                    datArr(i, 3) = locArr(j, 2)
                    datArr(i, 4) = locArr(j, 3)
                    Exit For
                End If
            Next j
        Next i
        shDat.Cells(1).Resize(UBound(datArr, 1), UBound(datArr, 2)) = datArr
    Application.ScreenUpdating = True
    End Sub

  4. #4
    Knowledge Base Approver VBAX Wizard
    Apr 2012
    Sub M_snb()
      sn = Sheet1.Cells(1).CurrentRegion.Resize(, 4)
      sp = Sheet2.Cells(1).CurrentRegion
      With CreateObject("scripting.dictionary")
        For j = 2 To UBound(sn)
          .Item(sn(j, 1)) = Array(sn(j, 1), "", "", "")
        For j = 2 To UBound(sp)
          For Each it In Filter(.keys, sp(j, 1))
            .Item(it) = Array(it, sp(j, 1), sp(j, 2), sp(j, 3))
        Sheet1.Cells(30, 1).Resize(.Count, 4) = Application.Index(.items, 0, 0)
      End With
    End Sub
    or using simple formulae
    Like this arrayformula:
    PHP Code:
    Attached Files Attached Files
    Last edited by snb; 10-01-2022 at 10:15 AM.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Oct 2005
    Surrey UK
    You can do this without vba, with Power Query.
    The table at cell A1 of the Data sheet is a proper Excel Table named Table1
    The table at cell A1 of the Location sheet is a proper Excel Table named Table2
    If you change the data in either of those tables you'll need to refresh the ResultTable table at cell C1 of the Data sheet by right-clicking it and choosing Refresh.

    Sheet1 also contains a formula solution in cell B2 which is copied down.
    Attached Files Attached Files
    Last edited by p45cal; 10-02-2022 at 02:30 PM.
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts