please explain the lambda function
Aaaaagh!
First, there's very, but
very, likely a much more straightforward solution than the one I've produced; it's just the route I ended up taking after experimentation and the first one that seemed to work.
Look at sheet
Sheet2 (2) of the attached where I've put the full version of the lambda formula in cell H4, and put it onto several lines to try to make it easier to read.
A lambda function takes on the arguments in brackets after it, in the order they appear in:
Within a LET statement, it's the last item that is returned (
result in the picture above).
In cells H6:H11 I've used the same formula but changed the last item of the LET statement so that you can see what each item returns.
Here's the one for
LSide:
(It's showing zeroes for spaces but it doesn't matter.
Edit: If you're looking for repeating zeroes it
does matter. The formula would need a tweak to correct for this.)
The most difficult items to understand are
RMatch and
LMatch; I've used XMATCH to search for the first cell which doesn't contain the repeating value you're looking for, searching from the right end for the
LSide, and from the left for
RSide. The IFERROR bits are to cater for when all the cells contain the repeating value when XMATCH would return #N/A, so the count in that case is the same as the number of columns in
LSide/RSide.
result is the SUM of
LMatch and
RMatch, but since the
LSide and
RSide overlap by one cell that cell gets counted twice so 1 is subtracted, but if there are no matching values that would result in -1, hence the MAX function.
Note that
Acell, FullRng, repeatingValue, SplitColm, LSide, RSide, LMatch, RMatch &
result, are all 'variable names' that I've dreamt up and are not built-in Excel functions. You can replace them with your own names.
That's as far as I'm prepared to go.
If I happen to come across a more straightforward formula I'll post again.
