Sleeper: Find a Partial Match from Location Sheet

[sethu29]

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.


30172

[arnelgp]

here is another way to do it.

[jolivanes]

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

[snb]

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:

=INDEX(Location!$A$2:$A$14;MATCH(FALSE;ISERR(FIND(Location!$A$2:$A$14;Data! $A2));0))

[p45cal]

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.