Redimensioning 18

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.

More…

Simultaneous Locating of First and Last Numbers in a String 23

I was initially debating whether to give this post a more pragmatic title, such as “Extracting Phone Numbers from a String”, that being one of the more common practical applications for the techniques outlined here.

However, the extraction of phone numbers (I’m referring here to that type which employs some form of delimiter, e.g. 1-800-12345, and not that which comprises a non-delimited numerical string, e.g. 180012345, there existing already well-documented formula techniques for the extraction of the latter – although of course the set-up given here will work for those as well) is certainly not the only use for this method, and so, in the end, I chose to go with a less restrictive, more theoretical title.

More…

VLOOKUP Across Several Worksheets (2) – Multiple Search Criteria 9

I recently made the post here, in which I presented a solution to the problem of returning a value based upon matching a single criterion in a given column across multiple worksheets.

In this follow-up post I will look at the analogous case in which we are not matching a single criterion, but several. As mentioned in the first instalment, I will look at two solutions to this problem, one in which we make use of an extra “helper” column in each of the relevant worksheets, and one in which we do without such aids.

More…

Coercing array returns from CSE-resistant formulas 9

We usually face no problems in cases where we wish to apply a formula to, not just one, but an array of values. And of course we do this by simply committing the formula as an array formula, i.e. with CSE.

However, not all formulas yield so easily, and some stubbornly resist any attempts at coercing an array of returns from them. Here I would like to discuss some techniques which, in addition to array-entry, can help coerce the desired result.

The principal method in such cases is to use a construction involving OFFSET, though a set-up using INDEX is equally viable; indeed, due to its non-volatility, perhaps even preferable. Some cases may require even more coercion than that, and others less. But the one thing they all share in common is that, on its own, array-entry just isn’t enough!

More…