# 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.

We may also face a situation in which we wish to pass the elements of a two-dimensional array to some function, though, if that function is one which does not operate over two-dimensional arrays, we must again first perform a prior redimensioning of our array.

To illustrate what I mean, let’s take a simple example. Given the data below (you can download the workbook here): we can construct various statements, such as:

`=MID(A1,1,1)`

giving of course “A”.

We can quite naturally also construct, e.g.:

`=MID(A1,{1,2},1)`

the resulting array from which will be the one-dimensional, in this case single-row vector:

`{"A","m"}`

We can of course construct the equivalent single-column vector, i.e.:

`{"A";"m"}`

by using the variation:

`=MID(A1,{1;2},1)`

as I hope most readers know.

And naturally the equivalent results for A2, A3, A4, etc., using the first variation, would be:

`{"E","s"}`
`{"P","e"}`
`{"C","e"}`

etc., etc. Needless to say we also have no problems in going one dimension further. For example, the formula:

`=MID(A1,{1,2},{1;2;3})`

will produce the two-dimensional, 3-row-by-2-column array:

`{"A","m";"Am","ma";"Ama","map"}`

Regular readers will also be aware of the importance of the two arrays in the above construction being orthogonal to each other. Here, MID’s start_num parameter, i.e. {1,2}, is a single-row vector, whereas the num_chars parameter, i.e. {1;2;3}, is a single-column vector.

We could of course reverse the vector-type of these two parameters. Provided that they are orthogonal, the entries in the resulting arrays will be identical. It should be noted, however, that their dimensionality will not be equal: whereas the above is a 3-row-by-2-column array, the result of:

`=MID(A1,{1;2},{1,2,3})`

i.e.:

`{"A","Am","Ama";"m","ma","map"}`

is this time a 2-row-by-3-column array.

Nevertheless, each is merely a transposition of the other and so, in general, it is not important which we construct. What is important is that we have succeeded in generating an array consisting of six results, corresponding to the six substrings obtained from A1 via, for each of our two starting positions (1 and 2), using three string lengths (1, 2 and 3).

And it is crucial to be aware that this can only be done if the two arrays being passed to MID are orthogonal. Were we instead to attempt:

`=MID(A1,{1,2},{1,2,3})`

we would obtain an array consisting of, not 6 elements, but a mere 3, and one of those an error to boot, viz:

`{"A","ma",#N/A}`

For those readers who are not sure why this is so, the reason is that, when two arrays are of the same vector-type, i.e. both are single-row arrays or both are single-column arrays, Excel “pairs” each element from one array with the element in the corresponding position in the other.

Hence, the three elements in the array resulting from:

`=MID(A1,{1;2},{1,2,3})`

are equivalent to performing each of the following three individual constructions:

`=MID(A1,1,1)`

i.e. “A”

`=MID(A1,2,2)`

i.e. “ma”, and finally…

…well, there simply is no third element in the array being passed as MID’s start_num parameter to pair up with the third element in the num_chars parameter. As such, Excel – quite rightly – returns #N/A as the third element in the resulting array.

I say “quite rightly”, though perhaps we should not take this result for granted. After all, the default behaviour in such cases could have been set so that such constructions flat-out error, rather than giving us an array to work with, albeit one containing errors.

In fact, what has happened is that Excel, in an attempt to resolve the issue of being passed two arrays of differing size, has artificially redimensioned the smaller of the two so that it matches the larger. In doing so, any additional, unpairable cells in the resulting array are filled with #N/As.

Despite these error values, however, and, as I mentioned in a previous post, it could be argued that this is preferable to rejecting the entire formula out of hand. Indeed, there may well be situations where we knowingly accept that one of our arrays is being “re-dimensioned”, even filled with errors, providing that we then manipulate the resulting array as befits our needs.

All good so far, then. What’s more, we all know that we can pass more than a single cell as the range to the vast majority of functions. As such, we can also legitimately construct:

`=MID(A1:A9,1,1)`

which returns a one-dimensional, 9-row-by-1-column array consisting of the first characters in each of the strings in A1:A9, i.e.:

`{"A";"E";"P";"C";"R";"B";"M";"A";"A"}`

And, just as we were able to form nine separate constructions, one for each of the values in A1:A9, viz:

`=MID(A1,{1,2},1)`

we can also extend that set-up by passing the entire range A1:A9:

`=MID(A1:A9,{1,2},1)`

and this time we have a two-dimensional, 9-row-by-2-column array:

`{"A","m";"E","s";"P","e";"C","e";"R","i";"B","a";"M","a";"A","m";"A","c"}`

Naturally, A1:A9 being a column-vector, our array for MID’s start_num parameter must be a row-vector. Attempting instead:

`=MID(A1:A9,{1;2},1)`

would end in disaster, as readers can easily verify.

But how about going one dimension further? What if we want to calculate:

`=MID(A1:C9,{1,2},1)`

which, we would hope, will return an array consisting of 54 elements, equivalent to the results of the 54 individual MID constructions:

`=MID(A1,1,1)`
`=MID(A1,2,1)`
`=MID(A2,1,1)`
`=MID(A2,2,1)`

etc., etc.?

The answer, unfortunately, is that we cannot. At least, not without further manipulation, that is. In fact, the result of our attempt will be an array consisting of, not 54, but a mere 27 elements, viz:

`{"A","ã",#N/A;"E","a",#N/A;"P","l",#N/A;"C","a",#N/A;"R","o",#N/A;"B","i",#N/A;"M","o",#N/A;"A","i",#N/A;"A","i",#N/A}`

I would here like to draw the attention of readers to this previous post of mine:

COUNTIFS: Multiple “OR” criteria for one or two criteria_ranges

which features an explanation of “dimensionality” with regard to arrays, and so will be of benefit to readers who are uncertain as to how the above 27-element array could possibly be the result of our construction.

Readers may also like to work out for themselves why the equivalent construction, though with the vector-type of the second parameter reversed, i.e.:

`=MID(A1:C9,{1;2},1)`

results in:

`{"A","S","P";"s","a","a";#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A}`

an even worse result, one might argue.

So what are we to do? Does this inherent limitation of Excel’s regarding the number of dimensions in which we can operate mean that we must give up on our attempts to obtain this array of 54 elements we are seeking?

Well, no, not quite. Whilst it is true that we cannot change the number of dimensions we have at our disposal, that it is not to say that we cannot achieve our goal via other means.

And that brings me nicely to the whole point of this post: the technique of redimensioning, the basic premise of which is to take a two-dimensional array and reduce it to a one-dimensional array, whilst of course maintaining the elements within that array.

Having achieved that redimensioning, we are then free to pass that new array to some further function operating over an array of parameters. This will of course create a further two-dimensional array of results, which, should we wish to pass to yet another function using an array of parameters, will again require redimensioning back to one of just a single dimension. And so on and so on.

In fact, there is nothing to say (limitations aside) that we cannot continue to redimension an array as many times as we wish, effectively meaning that we can perform multi-dimensional functional constructions which Excel would otherwise baulk at.

Before returning to our MID example, let’s take a slightly different example as means of explanation. Supposing we have the following in A1:E10: Clearly the data here is two-dimensional. A 10-row-by-5-column array, to be precise, whose Excel representation is:

`{"A1","B1","C1","D1","E1";"A2","B2","C2","D2","E2";"A3","B3","C3","D3","E3";"A4","B4","C4","D4","E4";"A5","B5","C5","D5","E5";"A6","B6","C6","D6","E6";"A7","B7","C7","D7","E7";"A8","B8","C8","D8","E8";"A9","B9","C9","D9","E9";"A10","B10","C10","D10","E10"}`

But what if, for various reasons, we require the above data in a one-dimensional array? That is, can we form:

`{"A1";"B1";"C1";"D1";"E1";"A2";"B2";"C2";"D2";"E2";"A3";"B3";"C3";"D3";"E3";"A4";"B4";"C4";"D4";"E4";"A5";"B5";"C5";"D5";"E5";"A6";"B6";"C6";"D6";"E6";"A7";"B7";"C7";"D7";"E7";"A8";"B8";"C8";"D8";"E8";"A9";"B9";"C9";"D9";"E9";"A10";"B10";"C10";"D10";"E10"}`

i.e. a 50-row-by-1-column array?

(Or, if you prefer:

`{"A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3","A4","B4","C4","D4","E4","A5","B5","C5","D5","E5","A6","B6","C6","D6","E6","A7","B7","C7","D7","E7","A8","B8","C8","D8","E8","A9","B9","C9","D9","E9","A10","B10","C10","D10","E10"}`

this time a 1-row-by-50-column array.)

The answer is yes, and the traditional technique is via the following:

`=INDEX(A1:E10,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1)/COLUMNS(A1:E10))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1),COLUMNS(A1:E10))))))`

the idea of which is to INDEX each and every element within this two-dimensional array precisely once, the above being equivalent to:

`=INDEX(A1:E10,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4;5;5;5;5;5;6;6;6;6;6;7;7;7;7;7;8;8;8;8;8;9;9;9;9;9;10;10;10;10;10})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))`

The key point to note is that the arrays here being passed to INDEX – that for the row_num and that for the column_num parameter – are both of the same vector-type (both are column-vectors), thus ensuring that the resulting array produced by INDEX is also of that vector-type. And a column-vector is of course one-dimensional by its very definition.

It can easily be verified that the above resolves to the single-dimension array given previously, as required. Readers who have not seen the necessary coercion technique with:

`N(IF(1,`

should see here for details:

INDEX: Returning an array of values

There is also an explanation of the two near-identical constructions being used for INDEX’s two parameters midway through the post here:

Unique, Alphabetical List from Several Columns

An extremely useful technique and one which I have used on numerous occasions. But are there alternative set-ups available for performing such a redimensioning? The above construction is perfectly good, though it could be argued that it suffers from being a touch on the lengthy side, even the more so if we were to attempt more than one redimensioning within the same formula.

The answer is yes, at the cost of a touch of volatility and provided that the redimensioning is being performed on an actual worksheet range. And it should be emphasized that there the INDEX construction has an undeniable advantage, in that we can employ it to redimension not just ranges within the worksheet, but also those resulting from some other subfunctions within the formula.

However, subject to that caveat, we can use the much shorter:

`=T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0))`

How does it work?

First, note that the above construction employs the function T, and hence requires that the values in the range A1:E10 be non-numeric. This can naturally be replaced with N for a range consisting of numeric values. For ranges comprising mixed datatypes, I recommend the INDEX approach (though I am also hoping that another of Lori’s groundbreaking discoveries in the field of dereferencing may be employable here also).

(Post-script: it turns out that we can indeed utilize that discovery to good effect, viz:

`=+INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0)`

which will return mixed datatypes, and all at the cost of replacing N (or T) with the evidently more flexible unary plus operator. Thanks, Lori.)

The point is to take advantage of the property of MODE.MULT of returning a one-dimensional array of returns, which is equally true whether the array being passed to that function is itself one- or two-dimensional.

In fact, this property is relatively rare, so we must be thankful once again to this most versatile of functions. FREQUENCY is another function which returns a single-dimensioned array, and indeed it is possible to construct a set-up using that function for the purpose of redimensioning. I will not present it here, however; suffice to say that it is necessarily more convoluted, a matter not helped by that function’s habit of returning an array comprising one more element than that originally passed to it.

So MODE.MULT it is, then. And, having just pointed out a potential drawback of FREQUENCY, I might also add that MODE.MULT is not itself faultless: the fact that this function errors if no one element within the array does not occur at least once means that we often have to force the issue, as it were.

Naturally this is not at all difficult under normal circumstances. Given a one-dimensional array, for example, we can simply duplicate each element within that array by creating the equivalent two-dimensional version, obtained via taking the product of the one-dimensional array with a suitable, orthogonal array, i.e. {1,1} or {1;1}, depending on the vector-type of the original array in question.

Here, though, things are not so simple. Indeed, if we were to attempt the direct:

`MODE.MULT({1,1}*(10^5*ROW(A1:E10)+COLUMN(A1:E10)))`

we would return an error, since this is equivalent to:

`MODE.MULT({1,1}*{100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005})`

and, our second array not being one of just a single dimension, we cannot legitimately form the product between it and another, one-dimensional array.

We need to be a bit craftier here. Hence the deliberate use of the extended range – A1:E20 – within this construction, since:

`1+MOD(ROW(A1:E20)-1,10)`

which is:

`1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1,10)`

i.e.:

`1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},10)`

i.e.:

`1+{0;1;2;3;4;5;6;7;8;9;0;1;2;3;4;5;6;7;8;9}`

is finally:

`{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}`

and we have succeeded in using an alternative, albeit longer, method for duplicating each of the 10 required entries within our array. As such, we can be sure that MODE.MULT will be happy, and so our construction, i.e.:

`T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0))`

is now:

`T(INDIRECT(TEXT(MODE.MULT(10^5*{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}+COLUMN(A1:E10)),"R0C00000"),0))`

which becomes:

`T(INDIRECT(TEXT(MODE.MULT({100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000;100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000}+{1,2,3,4,5}),"R0C00000"),0))`

i.e.:

`T(INDIRECT(TEXT(MODE.MULT({100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005;100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005}),"R0C00000"),0))`

and now MODE.MULT can do its work, giving:

`T(INDIRECT(TEXT({100001;100002;100003;100004;100005;200001;200002;200003;200004;200005;300001;300002;300003;300004;300005;400001;400002;400003;400004;400005;500001;500002;500003;500004;500005;600001;600002;600003;600004;600005;700001;700002;700003;700004;700005;800001;800002;800003;800004;800005;900001;900002;900003;900004;900005;1000001;1000002;1000003;1000004;1000005},"R0C00000"),0))`

and the remainder may be familiar to some readers, the above resolving to:

`T(INDIRECT({"R1C00001";"R1C00002";"R1C00003";"R1C00004";"R1C00005";"R2C00001";"R2C00002";"R2C00003";"R2C00004";"R2C00005";"R3C00001";"R3C00002";"R3C00003";"R3C00004";"R3C00005";"R4C00001";"R4C00002";"R4C00003";"R4C00004";"R4C00005";"R5C00001";"R5C00002";"R5C00003";"R5C00004";"R5C00005";"R6C00001";"R6C00002";"R6C00003";"R6C00004";"R6C00005";"R7C00001";"R7C00002";"R7C00003";"R7C00004";"R7C00005";"R8C00001";"R8C00002";"R8C00003";"R8C00004";"R8C00005";"R9C00001";"R9C00002";"R9C00003";"R9C00004";"R9C00005";"R10C00001";"R10C00002";"R10C00003";"R10C00004";"R10C00005"},0))`

which is, finally:

`{"A1";"B1";"C1";"D1";"E1";"A2";"B2";"C2";"D2";"E2";"A3";"B3";"C3";"D3";"E3";"A4";"B4";"C4";"D4";"E4";"A5";"B5";"C5";"D5";"E5";"A6";"B6";"C6";"D6";"E6";"A7";"B7";"C7";"D7";"E7";"A8";"B8";"C8";"D8";"E8";"A9";"B9";"C9";"D9";"E9";"A10";"B10";"C10";"D10";"E10"}`

a one-dimensional version of our original two-dimensional array, as required.

So we have seen how a two-dimensional array can be reduced to one of just a single dimension. Returning to our MID example, in which if you recall we were attempting to evaluate the construction:

`=MID(A1:C9,{1,2},1)`

in the hope of generating an array consisting of all 54 permutations given those parameters, though coming unstuck after having “run out of dimensions”.

Using our technique of redimensioning, however, all is not lost. We simply replace A1:C9 with the one-dimensional equivalent we have just determined, viz:

`MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:C19)-1,10))+COLUMN(A1:C9)),"R0C00000"),0)),{1,2},1)`

which is simply:

`MID({"Amapá";"São Paulo";"Paraná";"Espírito Santo";"Santa Catarina";"Maranhão";"Pernambuco";"Alagoas";"Mato Grosso";"Ceará";"Paraíba";"Piauí";"Rio Grande do Sul";"Rondônia";"Tocantins";"Bahia";"Minas Gerais";"Roraima";"Mato Grosso do Sul";"Goiás";"Pará";"Amazonas";"Rio Grande do Norte";"Rio de Janeiro";"Acre";"Distrito Federal";"Sergipe"},{1,2},1)`

Whereas previously the array being passed to MID – A1:C9 – was two-dimensional, and hence caused us issues when we attempted to evaluate that construction, here we are passing a one-dimensional array as the range. As such, we have enough dimensions to spare, the above resolving to:

`{"A","m";"S","ã";"P","a";"E","s";"S","a";"M","a";"P","e";"A","l";"M","a";"C","e";"P","a";"P","i";"R","i";"R","o";"T","o";"B","a";"M","i";"R","o";"M","a";"G","o";"P","a";"A","m";"R","i";"R","i";"A","c";"D","i";"S","e"}`

precisely the 54 elements we were hoping to generate.

And, if we really want, we don’t have to stop there. Using these techniques, we can even evaluate “four-dimensional” constructions such as:

`=MID(A1:C9,{1,2},{1;2;3})`

though, since the second stage in the necessary “double-redimensioning” will involve the array generated above, and since that array is not an actual worksheet range, we will be unable to employ our MODE.MULT technique a second time. Instead, for that second redimensioning we must turn to the aforementioned INDEX set-up.

All in all, the above can be achieved using:

`=MID(INDEX(ReDim1,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1)/COLUMNS(ReDim1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1),COLUMNS(ReDim1)))))),1,{1,2,3})`

where ReDim1 is a slight variation on our first redimensioning, viz:

`=MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(\$A\$1:\$C\$19)-1,10))+COLUMN(\$A\$1:\$C\$9)),"R0C00000"),0)),{1,2},MAX(LEN(\$A\$1:\$C\$9)))`

Another post to follow shortly. Watch this space!

1. I’ve not read this post yet, just passing by to comment that I liked you used Brazilian states. Where are you from?

2. Hi Felipe.

To be honest I just chose Brazil at random, though I’m glad that this choice has pleased someone!

And perhaps also that choice wasn’t as random as I thought it was, knowing that several of the regular visitors to this site – including yourself – are from South America.

Até mais tarde!

3. Flora says:

XOR, very sophisticated formulas, I understood all your previous posts, but this one, i could not figure out with this two different type of formula what is achieved. becuase when i opened the downloaded workbook. all i can see in the result of formula is a zero and nothing else. am i missing something?

4. @Flora

For the formulas which return arrays of values (which is most of them in this post), naturally you will not see the individual entries from those arrays within the actual worksheet.

However, I still wanted to give readers the ability to follow the evaluation of those formulas for themselves, as an accompaniment to my explanations.

Multi-cell array formulas were an option, and one which would allow the results of the final array to be displayed in individual worksheet cells, though I personally tend to avoid this type of array formula.

Hence I chose to array-enter the formulas in the worksheet, and to wrap in some random array-processing formula (SUM – hence the results of 0), so that the array output would be generated and so be visible when evaluating the formulas using the Evaluate Formula tool.

Are you familiar with this feature? If so, you can deconstruct the formulas in the workbook that way and see the results of any arrays generated, including the important final one (even if, as I said, you will not be able to see these arrays within the actual cell).

There is also the option of “piecemeal” evaluation of parts of the formula via going into the formula bar, selecting certain parts of the formula, and pressing F9. I would point out that this method can sometimes lead to erroneous “evaluations” (though it should be fine here).

Hope that helps.

5. Isaac says:

XOR — Thanks for taking the time to provide such comprehensive explanations of the ideas you present.

I’m a long-time excel user, but a beginner in manipulating arrays. I have a questions I know you can help with:

Suppose I had a formula that would conceptually work thus:

`=MATCH({1,1},{0,1;0,0;1,1;1,0})`

to find the sub-array {1,1} in the look-up array {0,1;0,0;1,1;1,0}

In my imagination, this conceptual formula will return the value 3 because {1,1} matches the third row of the lookup array.

Is there a formula that will actually perform this way?

6. @Isaac

Apologies for the late reply – been away for a while.

Many thanks. I’m glad you appreciate the posts I make here.

As to your question, you can use MMULT, e.g.:

`=MATCH(TRUE,MMULT({0,1;0,0;1,1;1,0},{1;1})=2,0)`

Hope that helps. Let me know if you have any follow-up questions.

Regards

7. excelfeast says:

I posted a redimensioning challenge some time back at

and came up with the following, where a is a named range covering the 2d array.

Row Vector output:

`=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(-a)))/COLUMNS(a)-1/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(-a)))-1,COLUMNS(a)),1,1)`

Column Vector Output:

`=OFFSET(a,MOD(ROW(OFFSET(A1,,,COUNTA(-a)))-1,ROWS(a)),INT(ROW(OFFSET(A1,,,COUNTA(a)))/ROWS(a)-1/ROWS(a)),1,1)`
8. Sorry, that last comment didn’t have my name. Jeff Weir here.

9. The INDEX based formula you presented has the advantage that it handles mixed data types. To get the MODE.MULT one doing that, you need to do some serious repitition using an IF statement. Here’s how it looks with the IF arguments exploded:

```=IF(

T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0))="",

N(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0)),

T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0)))```

Similarly, my OFFSET formula needs the same treatment:

```=IF(
T(OFFSET(A1:E10,MOD(ROW(OFFSET(A1,,,COUNTA(-A1:E10)))-1,ROWS(A1:E10)),INT(ROW(OFFSET(A1,,,COUNTA(A1:E10)))/ROWS(A1:E10)-1/ROWS(A1:E10)),1,1) )="",

N(OFFSET(A1:E10,MOD(ROW(OFFSET(A1,,,COUNTA(-A1:E10)))-1,ROWS(A1:E10)),INT(ROW(OFFSET(A1,,,COUNTA(A1:E10)))/ROWS(A1:E10)-1/ROWS(A1:E10)),1,1) ),

T(OFFSET(A1:E10,MOD(ROW(OFFSET(A1,,,COUNTA(-A1:E10)))-1,ROWS(A1:E10)),INT(ROW(OFFSET(A1,,,COUNTA(A1:E10)))/ROWS(A1:E10)-1/ROWS(A1:E10)),1,1) ))```
10. @Jeff

Thanks a lot.

Actually, as I intimated in the post, it turns out that we can return multiple datatypes – without the need to “show our hand” by choosing either N or T – by the addition of just a single character (i.e. the unary plus), viz:

`=+INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0)`

Another of Lori’s astonishing discoveries, which is discussed here:

http://stackoverflow.com/questions/31090509/what-does-the-unary-plus-operator-do-in-excel-formulas

Opens up many new possibilities, such as here…

Cheers

11. Interesting. I note that while this construction correctly returns an array to an individual cell, if you array enter the formula over a range you just get a whole bunch of #VALUE! errors.

12. Ah yes, I see now that I skipped over the bit where you’d discussed your preference for INDEX in the event of mixed data types.

Where you say:

However, subject to that caveat, we can use the much shorter:

`=T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),"R0C00000"),0))`

…I feel duty bound to point out that it is only that much shorter because you have hardcoded both the size of the doubled range into the formula, while at the same time the formula relies on the upper left cell of the 2d range being located at A1)

By comparison, with the INDEX-based formula you can replace the A1:E10 reference with the named range d and move it anywhere in the spreadsheet, and the formula will still work.

So to be “fair” to INDEX, you really need to replace the hard-coded bits of the MODE.MULT alternative with formula-derived alternatives.

MODE.MULT (156 characters)

`=+(INDIRECT(TEXT(MODE.MULT(10^5*(MIN(ROW(d))+MOD(ROW(OFFSET(d,,,ROWS(d)*2))-MIN(ROW(d)),ROWS(d)))+COLUMN(d)-MIN(COLUMN(d))+MIN(COLUMN(d))),"R0C00000"),0))`

INDEX (173 characters)

`=INDEX(d,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(d)*COLUMNS(d)))-1)/COLUMNS(d))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(d)*COLUMNS(d)))-1),COLUMNS(d))))))`

What’s more, the INDEX seems about 10 times faster than the MODE.MULT version.

13. Here’s something similar to this challenge. The following formula will redimension 3 non-contiguous 1d ranges into a 1d range:

`=INDIRECT(ADDRESS(MOD(SMALL(IFERROR(CHOOSE({1;2;3},COLUMN(x),COLUMN(y),COLUMN(z))^0*CHOOSE({1;2;3},ROW(x),ROW(y),ROW(z))+(2^14*{0;1;2}),""),ROW(OFFSET(A1,,,COUNTA(x,y,z)))),2^14),MOD(SMALL(IFERROR(CHOOSE({1;2;3},COLUMN(x),COLUMN(y),COLUMN(z))+(2^14*{0;1;2}),""),ROW(OFFSET(A1,,,COUNTA(x,y,z)))),2^14)))`

…where x, y, and z are three non-contiguous horizontal 1d ranges.

This comes from a challenge I posted sometime back at

14. Lori says:

The idea for using of a + sign here is due to Colin Legg but he found some limitations with it, for example it throws error alerts when put inside some functions like MEDIAN(). You can add the CELL() / IF() combination to make it robust as was discussed recently:

Perhaps you missed the later comments, Jeff? That thread actually links back to this one so we now have a nice circular reference 🙂

15. @Jeff

Again, many thanks for your excellent contributions. And also for pointing out the shoddiness in some of my work in this post.

You are right. The MODE.MULT set-up I give is not at all flexible, and would require the modifications you point out. I think I was so excited at the time to have discovered an alternative, original means to redimension that I lost some of my usual rigour.

Still, it’s interesting from a theoretical point of view, even if the INDEX set-up is still preferable (and especially in light of the additional comments you made re calculation speed).

By the way, the main advantage of the INDEX approach is not so much that it handles mixed datatypes, but rather that – unlike your OFFSET set-up – does not require that the range passed to it be an actual worksheet range. As such, and as I mentioned in the post, we can use it to redimension a range which has been generated in-formula, something which is not possible using OFFSET.

Thanks again for all your input and work.

16. Yeah, that INDEX is cool! So is the MODE.MULT…I seem to remember using FREQUENCY in a formula challenge and struggling to ditch the extra element on the end, so now that I know your MODE.MULT trick I’ll have to try to remember what challenge it was and have another crack at it.

Thanks for the challenge.

@Lori – full circle!

17. Here’s another way to de-reference:

`=INDIRECT("R"&MIN(ROW(_2D_Mixed))+TRANSPOSE(INT((COLUMN(\$A\$1:INDEX(1:1,ROWS(_2D_Mixed)*COLUMNS(_2D_Mixed)))-1)/ROWS(_2D_Mixed)))&"C"&MIN(COLUMN(_2D_Mixed))-1+COLUMNS(_2D_Mixed)+MOD(ROW(\$A\$1:INDEX(A:A,ROWS(_2D_Mixed)*COLUMNS(_2D_Mixed))),-COLUMNS(_2D_Mixed)),FALSE)`
18. Sorry, I meant redimension, not de-reference. Although this redimensioned array acts pretty weird…it evaluates to what you want, but you seem to need to have to dereference it to get it array entered in the sheet.

19. Alex Platonov says:

Hi XOR LX,

Very interesting website! Learned from it a lot. Thank you and all who wrote comments! I see many famous people form excel/vba world there =)

From time to time I stumble upon the need of concatenation of non-contagious ranges and never found a solution. After reading this topic I thought I can do it this way through INDIRECT, but didn’t succeed. I read that it is impossible. Is it? I’m asking because I thought many things are impossible in excel until I read this website =)

Thank you
Alex

20. Alex Platonov says:

*sorry, not non-contagious ranges, but any dynamic ranges.

21. Hi Alex,

Thanks for your kind words re the work I do here.

As to your question, can you give a small example to illustrate what precisely you mean by ‘concatenation’? Do you mean that you want to create a single array comprising all entries from several ranges? Are the entries in question text, numeric, or a mixture of both?

As I mentioned, probably best if you give a small example together with expected results.

Regards

22. Alex Platonov says:

Hi XOR LX,

Thank you for the reply. I should have done it from the beginning. Sorry.

A1:A5 (mixed type)
String1
2
String3
4
String5

B1:B2 (can change)
2
3

I want to be able to do something like this:

`=CONCATENATE(INDEX(A:A,B1):INDEX(A:A,B2))`

and get:

`=A2&A3&A4=2String34`

If it’s possible to use a custom separator would be great.

Thank you
Alex

23. Ok, thanks, I understand now.

This is straightforward if you have an Office 365 Subscription and therefore access to functions like TEXTJOIN. Without, I’m afraid there’s no feasible non-VBA way to achieve your desired result.

Regards

24. Alexander Platonov says:

Thank you, XOR LX!
Yes, I knew about TEXTJOIN. But what is the fun of using it =) We are not looking for easy ways =)

Thanks
Alex

This site uses Akismet to reduce spam. Learn how your comment data is processed.