Forum Discussion
Search and return values from a string
Hi, I have an item list as string in one column separated by "|" and I have a mapping table of the item list. What I need to do is compare the string with the mapping table and return all the it...
A VBA UDF also works:
Option Explicit
Public Function ShowMissingItems(TheText As String, MappingTable) As String
Dim vMappingTable As Variant
Dim lRow As Long
If TypeName(MappingTable) = "Range" Then
vMappingTable = MappingTable.Value2
ElseIf TypeName(MappingTable) = "Variant" Then
vMappingTable = MappingTable
End If
On Error Resume Next
For lRow = LBound(vMappingTable, 1) To UBound(vMappingTable, 1)
If InStr(TheText, vMappingTable(lRow, 1)) = 0 Then
ShowMissingItems = ShowMissingItems & vMappingTable(lRow, 1) & "|"
End If
Next
If Len(ShowMissingItems) > 1 Then
ShowMissingItems = Left(ShowMissingItems, Len(ShowMissingItems) - 1)
End If
End Function
Use function like so:
=ShowMissingItems(A2,'Mapping Table'!A2:A20)
Option Explicit
Public Function ShowMissingItems(TheText As String, MappingTable) As String
Dim vMappingTable As Variant
Dim lRow As Long
If TypeName(MappingTable) = "Range" Then
vMappingTable = MappingTable.Value2
ElseIf TypeName(MappingTable) = "Variant" Then
vMappingTable = MappingTable
End If
On Error Resume Next
For lRow = LBound(vMappingTable, 1) To UBound(vMappingTable, 1)
If InStr(TheText, vMappingTable(lRow, 1)) = 0 Then
ShowMissingItems = ShowMissingItems & vMappingTable(lRow, 1) & "|"
End If
Next
If Len(ShowMissingItems) > 1 Then
ShowMissingItems = Left(ShowMissingItems, Len(ShowMissingItems) - 1)
End If
End Function
Use function like so:
=ShowMissingItems(A2,'Mapping Table'!A2:A20)
Thank you so much! It worked and made my task hella lot easier