BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Posted by Andy Brown on 23 February 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Building a word search in Excel without macros
So last month I shared WOWSER (the Wise Owl Word Search Excel Replicator) using VBA macros. Andrew Howe of Transformaction asked about a non-macro version of a word search in Excel, and I - foolishly - expressed my opinion that it couldn't be done.
I reckoned without Excel formulae like this:
=IF(
target_spills,
0,
LET(
target,
INDEX(
grid_prev,
target_top_left_cellno + AbsOffsets0Arr
),
word,
IF(
K$4>0,
WordArr,
WordArrRev
),
IF(
SUM((LEN(target)>0)*(target<>word))>0,
0,
1 + SUM((LEN(target)>0)*(target=word))
)
)
)
This would be impressive enough, but many of the range names (shown in red above) are formulae themselves. For example, here's the formula for the range that the target_spills range above references:
=AND(Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)>=1,Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)<=Rows,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)>=1,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)<=Cols)
In addition to all of this, Andrew has included every Excel trick in the book, including the ones shown in the table below.
| Trick | Notes |
|---|---|
| The INDIRECT function | This can always be relied upon to do complicated things in Excel (the downside is that the resulting formulae can be very difficult to understand and test). |
| The LET function | A new Excel feature allowing you to use variables in Excel (I blogged about it recently). |
| Lambda functions | An even newer Excel feature which allows you to create your own functions in Excel without using VBA, like this. |
| The FILTER function | The new Excel dynamic array functions allow you to do all sorts of clever filtering, avoiding messy (and slow) SUMPRODUCT functions. |
| Relative ranges | Most people don't even realise that a range reference is stored as a formula rather than as an absolute reference (it's what allows you to create fancy things like dynamic range names in Excel). |
The results are impressive - there's something very satisfying about being able to press F9 to recalculate a workbook and seeing this:
One suggested solution for a set of related words.
To this:
Same grid size, same words ... totally different solution!
You can see how the algorithm works, how the formulae were built and other (much-needed!) documentation at Andrew's word search Excel lab page.
A word of advice: make yourself a strong cup of coffee before visiting this page!