Forum Discussion
Lookup five characters appearing in any order in a wordlist
Unfortunately, I don't know how to implement your formula, but on the plus side... I got it to work, finally, with a nested filter and an "or" function. I kept making little errors in the formula, which is why it wasn't working. And then I would try another approach, and another...
=FILTER(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),(RIGHT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=I57)+(LEFT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=H57))
The problem does draw one back. I had a go at using recursion to remove words that have any letter that is missing from the square. Although, on average, this requires far fewer comparisons, the calculation time only went down from 5sec to 3sec.
The methods I use have, in the main, only become possible with 365 and I have dropped almost all the standard practices of traditional spreadsheet development. The new and the traditional approaches share a common function library and work on a 2D grid but little else is similar.
My spreadsheet has been working well for the past month, but all of a sudden, I am getting an "empty array" error. I suspect a Microsoft update might be the culprit.
The spreadsheet offers to help resolve the error..,
To resolve the error, either change the criterion, or add the if_empty argument to the FILTER function. In this case, =FILTER(C3:D5,D3:D5<100,0) would return a 0 if there are no items in the array.
Here is my formula.
=IF(B2="Out","",FILTER(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),(RIGHT(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),1)=H2)+(LEFT(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),1)=G2)))
I don't know where to add additional if_empty arguments... I already have them in my formula. When I try to add another one, I get an error that there are too many arguments and excel rejects the change.
The formula appears in column AB. The #CALC has not previously been a problem. I would recalculate the spreadsheet using Shift F9, then filter out the blanks and #CALCs in column AB and only the solutions would remain.
But now, no solution words appear in the filter box, just blanks and #CALCs, because my formula no longer works. If I filter out blanks and #CALC, I would have nothing left. I tried it anyway and the spreadsheet would not respond.
I also tried going back to a previous version of the spreadsheet (I save them every day) thinking I may have accidentally changed something, but that isn't the case. I am running Windows 11 and Office 365.
I would appreciate any help.
Thanks,
Michael
- Mysteriously started working again...
