Jun 03 2021 01:56 PM
I'm Trying to Extract the Name from the Description if it matches our Customer Names List. Please see attached image
Here is What I want to do.
If Column B contains any word(Partial String Match) that matches with words in Column I and J then return the matched word in Columns G
Currently, My Formula is only finding the word on the same row, not the whole Columns
=IF(OR(COUNTIF(B2,"*"&[@Column1]&"*")),""&[@Column1]&"","")
Jun 03 2021 02:36 PM
In G2 as an array formula confirmed with Ctrl+Shift+Enter:
=IF([@Desc]="","",TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&TableName[[Column1]:[Column2]]&" "," "&[@Desc]&" ")),TableName[[Column1]:[Column2]],"")))
where TableName is the name of the table.
Jun 04 2021 12:14 PM
Thanks @HansVogelaar,
Your formula is working. However, it is returning the same string Many Times. Check attached Screenshot
Is there a way to keep only unique words?
Jun 04 2021 12:15 PM
Are you using Excel in Microsoft 365?
Jun 04 2021 12:17 PM
Jun 04 2021 12:49 PM
Thanks. so we can't use the new UNIQUE function.
A traditional formula is probably possible, but it'd be complicated. I'd prefer to use a custom VBA function - see the attached sample workbook. You'll have to allow macros when you open it.
This is the code:
Function GetMatches(s As String, rng As Range) As String
Dim dct As Object
Dim cel As Range
If s = "" Then Exit Function
Set dct = CreateObject(Class:="Scripting.Dictionary")
For Each cel In rng
If " " & s & " " Like "* " & cel.Value & " *" Then
dct(cel.Value) = 1
End If
Next cel
GetMatches = Join(dct.Keys, ", ")
End Function
And the formula:
=GetMatches([@Desc],TableName[[Column1]:[Column2]])