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 items that are NOT in the string.
Column X in Excel:
AAA|BBB|DDD|FFF|GGG|HHH
Mapping Table:
Column A Colimn B
AAA Item 1
BBB Item 2
CCC Item 3
DDD Item 4
EEE item 5
FFF Item 6
If you notice, CCC and EEE are missing from column X in excel. What I need to do is insert a new column Y next to column X and display the missing items: "CCC|EEE"
I am not great with macros, so any formulas to help with this would be highly appreciative. The items list is long, so cant do it manually.
Thanks
- 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)- Thank you so much! It worked and made my task hella lot easier
Hi Raj,
The easiest way is to use Power Query (Get&Transform in Excel 2016) as attached. If you are not familiar with it using formulas will be more complicated.
- Gotta love Power Query :)
Thank you for the response. Do not have Power Query currently and raised it with my company to get it installed. Curious to learn a bit more about it
