Oct 30 2022 08:39 AM
Oct 30 2022 08:39 AM
I hope you are fine. I have asked this question twice before, but the answers were not suitable for me. I don't want to do this with Power Query. I want to search all 10064 words in column j in each of the cells in the column. And the result will be exactly the same words and written in column B facing each cell A. .
The values of column A are hypothetical. In reality, the column has about 40,000 cells. And if the results include several words in each cell of column B, put them in one cell and put a comma between each word. Please write the formula for me.
I use Excel 365
Oct 30 2022 11:03 AM - edited Oct 30 2022 11:17 AM
You are not clear on what you want to "search all 10064 words" to find, and the special characters in column A make it less clear.
I assumed that you want each line (separated by Line Feed characters, CHAR(10)) within a cell to be matched, including any leading and/or trailing spaces. So, does this show the results you want (dscheikey's formula results shown in column C for comparison only) ?
(A6 and A9 do not match because the similar entries in column J have leading spaces. I do not understand why the "Dried Fruit " portion of A2 does not match J7.)
=IF(A2<>"", TEXTJOIN(",", TRUE, XLOOKUP( TEXTSPLIT(A2,CHAR(10)), J:J, J:J, "", 0, 1 ) ), "" )
Edit: Looking at it again, I see that J7 also includes a leading space, and thus does not match.
Oct 30 2022 11:11 AM
It might help others to look at solutions offered in other discussions from @jorj1991 .
Optimization and efficiency seem to be really important in this setup if previous solutions weren't acceptable. It's not clear if memory errors are the reason those solutions didn't work but I'll suggest that one thing to try with any lookups being done is to clean up and sort the words list in ascending order. Then try a VLOOKUP with an approximate match or even an XLOOKUP with a binary search. The tradeoff is the lookups are not as accurate but may at least calculate.
Nov 11 2022 07:10 AM
Hello, thank you for your reply and the time you took to help me. I apologize for the delay in replying. I tested your formula. does not work right. Maybe I didn't execute well, but this formula works easily.
=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$I$2:$I$37&"*"), $I$2:$I$37, ""))
"I" is the column to be searched in cell "A2".
Thank you again dear friend
Nov 11 2022 07:13 AM
Nov 11 2022 07:20 AM