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