Consulting

Results 1 to 5 of 5

Thread: Find a Partial Match from Location Sheet

  1. #1
    VBAX Regular
    Joined
    Feb 2012
    Posts
    46
    Location

    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.


    Problem.jpg
    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
    Joined
    Apr 2012
    Posts
    5,635
    or
    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), "", "", "")
        Next
         
        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))
          Next
        Next
         
        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:
    =INDEX(Location!$A$2:$A$14;MATCH(FALSE;ISERR(FIND(Location!$A$2:$A$14;Data!$A2));0)) 
    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
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    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
  •