Advanced Formula Challenge #12: Results and Discussion 5

Last week I set readers the challenge which can be found here.

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

Advanced Formula Challenge #12: An Array of Matches 81

The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.

For example, given the below in A1:E10:

An Array of Matches

the solution would be the array:


Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.

Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.

Also note that this is NOT a shortest formula challenge!

Solution next week. Good luck!

Redimensioning 24

Readers who have read some of my earlier posts will be familiar with the concept of “redimensioning” an array.

This is an extremely useful and important technique, which, in its basic form, allows us to take a two-dimensional array and convert it into one of just a single dimension, whilst of course retaining the elements within that array.

Such an approach is necessary if we wish to further manipulate the entries of some two-dimensional array. For example, we might be in a position in which, for whatever reason, we need to pass each of the entries in a two-dimensional array to an array of one or more parameters for further processing. However, since the evaluation of the resulting multi-dimensional “matrix” is not within Excel’s capabilities, we are obliged to first transform the original array to one of a single dimension.