Forum Discussion
Solved
finding several words in a column and avoiding duplicates
Hi everyone I have a very long question, so please bear with me as i try to explain my issue. I have list of several different types of analysis: Moisture/Loss on drying Ash/Residue on ...
Perhaps this one:
=LET( FindTerms, LAMBDA(row, LET(words, TEXTSPLIT(row, "/"), SUM(IFNA(XMATCH("*" & words & "*", ListofTerms, 2), 0))) ), BYROW(terms, FindTerms) )There's still the issue of using a wildcard match with terms with few characters such as "as" where we get a false positive where it matches "ashes".
Perhaps this one:
=LET(
FindTerms, LAMBDA(row,
LET(words, TEXTSPLIT(row, "/"), SUM(IFNA(XMATCH("*" & words & "*", ListofTerms, 2), 0)))
),
BYROW(terms, FindTerms)
)There's still the issue of using a wildcard match with terms with few characters such as "as" where we get a false positive where it matches "ashes".
Thank you for this! It saved me an entire worksheet and it works great. But as you said it still has the issues with As being found in ashes. Is it possible to maybe add a list of words it cannot match inside this formula? Because that is something I can easily make.
Thank you again
Vnibsen89
One solution could be to discard terms with a LEN of less than 3 so there are no false matches. For example:
=LET( FindTerms, LAMBDA(row, LET( prelim, TEXTSPLIT(row, "/"), words, FILTER(prelim, LEN(prelim) > 2), SUM(IFNA(XMATCH("*" & words & "*", ListofTerms, 2), 0)) ) ), BYROW(terms, FindTerms) )- Patrick2788
Thank you for your help - I will give it a try
