Sleeper: 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.


Attachment 30172

here is another way to do it.

Try so.

Code:

---

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

---

or

Code:

---

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)) 


---

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.