# Formula Challenge 013 - Turn non-contiguous cells into a 1D range

#### jeffreyweir

##### Active Member
Formula Challenge 013 - Turn non-contiguous cells into a 1D range

Challenge setup

Put the letter x in F7, and assign the name ‘x’ to it

Put the letter y in J6, and assign the name ‘y’ to it

Put the letter z in L18, and assign the name ‘z’ to it

Now write a formula that turns those three separate references into a 1D array
i.e. {"x","y","z"} and enter it over a one row by three column array.

No helper cells, no VBA, no working on this at 3am.

See if you can answer this by the end of TODAY()

My formula: 141 characters.

#### Sajan

##### Excel Ninja
Hi Jeff,

Not sure if I am interpreting your challenge properly... but how about

=CHOOSE({1,2,3},x,y,z)

-Sajan.

#### jeffreyweir

##### Active Member
Well, that was an easy challenge! Suffice to say I have something very tricky up my sleeve, and was trying to find a situation to apply it to. Obviously not this one. I might have to extend this to 2D arrays...watch this space.

#### jeffreyweir

##### Active Member
Okay, amended challenge.

Formula Challenge 013 - Turn non-contiguous cells into a 1D range

Challenge setup

Put the letters x,1 in cells F7:G7, and assign the name ‘x’ to this range

Put the letter y,2 in cells J6:K6, and assign the name ‘y’ to this range

Put the letter z,3 in cells L18:M18, and assign the name ‘z’ to this range

Now write a formula that turns those three separate references into a 1D array i.e. {"x",1,"y",2,"z",3} and enter it over a one row by six column array.

No helper cells, no VBA, no working on this at 3am.

See if you can answer this by the end of TODAY()

My formula: 199 characters, and is hard-coded to only accept 2*1 arrays for x,y,z.

#### jeffreyweir

##### Active Member
For extra credit, write a formula that accepts three 1d arrays of arbitrarily different lengths, and turns them into a 1D array.

Yes, it's possible. No, it's not easy. Yes, it's long (905 characters). No, I'm not looking forward to explaining it!

#### jeffreyweir

##### Active Member
Update...you can write a formula that accepts three 1d arrays of arbitrarily different lengths, and turns them into a 1D array in 308 characters. Plus one heck of a lot of thinking.

Look forward to someone trying this.

#### Luke M

##### Excel Ninja
Here's my shot. Handles 3 horizontal arrays (any length), output is horizontal.

=INDEX((x,y,z),1,COLUMN(A1)-COUNTA(x)*(COLUMN(A1)&#62;COUNTA(x))-COUNTA(y)*(COLUMN(A1)&#62;COUNTA(x,y)),1+(COLUMN(A1)&#62;COUNTA(x))+(COLUMN(A1)&#62;COUNTA(x,y)))

Length: 146

If the arrays are only numbers, could do it even smaller...

#### Sajan

##### Excel Ninja
Hi Luke,

Nice approach!

I think Jeff is looking for a formula that can be array-entered over a range of cells (even though your formula is more practical in real-world situations).

Here is a formula that needs to be array-entered over 6 cells (assuming (counta((x,y,z))=6)

=CELL("contents",INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(CHOOSE({1;2;3}, ROW(x)+COLUMN(x)%, ROW(y)+COLUMN(y)%, ROW(z)+COLUMN(z)%),COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z))))), "0.00"), ".", "C"),FALSE))

Cheers,

Sajan.

#### Sajan

##### Excel Ninja
Incidentally, here is a specific solution to Jeff's original question about handling three 2 column arrays:

=INDEX((x,y,z),1,{1,2,1,2,1,2},{1,1,2,2,3,3})

(Needs to be array-entered over 6 column cells)

Will look at generalizing this for Jeff's second question.

-Sajan.

#### Luke M

##### Excel Ninja
Thanks Sajan. I see what you mean about array entered...I suppose I could do this:

=INDEX((x,y,z),1,COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))-COUNTA(x)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x))-COUNTA(y)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x,y)),

1+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x))+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x,y)))

for 276 characters...but it's not nearly as pretty. =(

#### jeffreyweir

##### Active Member
Nice approach Luke. Although Sajan is correct in that I'm looking for a formula that you could use say as an argument to another function.

Sajan - your "Cell" approach doesn't work for me...all I get is Y1 if i enter it normally into once cell. If I array enter it, I get #N/A

That =INDEX((x,y,z) format is new to me...I didn't know you could do the (x,y,z) bit. But again, I'm looking for a formula that you could use say as an argument to another function. Array entering over the range not so important.

#### Sajan

##### Excel Ninja
Hi Jeff,

Not sure why the CELL approach is not working for you... It works in Excel 2010 for me.

Both of the above formulas need to be array-entered over a series of cells. i.e. they do not work in a single cell.

-Sajan.

#### jeffreyweir

##### Active Member
Apologies, Sajan...I was testing it on three 1d arrays of arbitrarily different lengths.

Good stuff Luke.

Okay lads, now how 'bout one that returns an array that you can reuse inside another formula. Or ratcheting up the stakes, one that returns an array that you can reuse inside another formula and also handles 1d arrays of arbitrarily different lengths.

Warning: It's a tough one #### jeffreyweir

##### Active Member
Luke: Your method of producing {1,2,3,4,1,2,1,2} via COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))-COUNTA(x)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x))-COUNTA(y)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))&#62;COUNTA(x,y)) is very very clever. I went a slightly different route.

#### sameer bhide

##### New Member
Jeff,

The below formula can be array entered across 6 cells - 157 Characters

[pre]
Code:
``````=INDEX((x,y,z),1,IFERROR(1/MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2),2),
(MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2)+COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))))/2)``````
[/pre]

#### jeffreyweir

##### Active Member
Sameer...I like the way you produce {1,2,1,2,1,2} via IFERROR(1/MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2),2)

and similary {1,1,2,2,3,3} from (MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2)+COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))))/2

But I want MORE!!! As per above, how 'bout one that returns an array that you can reuse inside another formula. Or ratcheting up the stakes, one that returns an array that you can reuse inside another formula and also handles 1d arrays of arbitrarily different lengths.

Just keep track of the billible consulting hours this takes up, and add it to my tab ;-)

#### Luke M

##### Excel Ninja
Thanks Jeff. I'll admit, I've never used that structure of INDEX in any of my personal work, but it was fun getting to learn it.

And me thinks you are beginning to sound like some of the forum posters, who start with a simple question, but keep adding scope to it... &#60;grin&#62; But it's a good challenge, so back to the dark cavern of XL I go...

#### Luke M

##### Excel Ninja
@Sajan

I like your trick of combining the row and column number with TEXT function and then substituting. Very clever.

#### Sajan

##### Excel Ninja
Thanks Luke. That is indeed a useful way to resolve to a specific cell's address quickly.

Jeff,

Here is another approach to get the same results for arbitrary length columnar x,y,z arrays:

=OFFSET(A\$1,SMALL(IFERROR(CHOOSE({1;2;3},ROW(x)*COLUMN(x)^0,ROW(y)*COLUMN(y)^0,ROW(z)*COLUMN(z)^0),FALSE),COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z)))))-1,SMALL(IFERROR(CHOOSE({1;2;3},COLUMN(x),COLUMN(y),COLUMN(z)),FALSE),COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z)))))-1)

If you want to array-enter the above results, then you would need to wrap the above in another OFFSET.

However, to pass the concatenation test, you would need to wrap this in an IF(T()="",N(),T()) construct.

i.e. if the above formula is named "FORMULA", then the following returns a valid array in a single cell.

=IF(T(FORMULA)="",N(FORMULA),T(FORMULA)) & "ABC"

However, the array-aware functions like COUNT, SUM, etc. operate on just the FORMULA above.

-Sajan.

#### jeffreyweir

##### Active Member
Try it on this:

x = F3:I3 = {"x1","x2","x3","x4"}

y = D2:E2 = {"y1","y2"}

z = K1:L1 = {"z1","z2"}

#### Sajan

##### Excel Ninja
Hi Jeff,

In my tests, x, y and z were in subsequent rows. In your sample data, they are random. As such, we just need to tweak the formula above slightly to keep the order of things:

=OFFSET(A\$1,ROUND(MOD(SMALL(IFERROR(CHOOSE({1;2;3},1+(ROW(x)*COLUMN(x)^0)%,2+(ROW(y)*COLUMN(y)^0)%,3+(ROW(z)*COLUMN(z)^0)%),FALSE), COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z))))),1)*100,0)-1,ROUND(MOD(SMALL(IFERROR(CHOOSE({1;2;3},1+COLUMN(x)%,2+COLUMN(y)%,3+COLUMN(z)%),FALSE), COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z))))),1)*100,0)-1)

Cheers,

Sajan.

#### jeffreyweir

##### Active Member
That % trick is very clever. Note that you can replace FALSE with "" and the ,0) in ROUND with ,)

Now of course you have to double the length of that puppy with an IF to handle text and numbers, unless *hint* you ditch the OFFSET in favor of something else.

#### jeffreyweir

##### Active Member
Instead of your % trick, I used something equally devious, and slightly shorter.

Yours:

ROUND(MOD(SMALL(IFERROR(CHOOSE({1;2;3},1+(ROW(x)*COLUMN(x)^0)%,2+(ROW(y)*COLUMN(y)^0)%,3+(ROW(z)*COLUMN(z)^0)%),""), COLUMN(OFFSET(A\$1,,,,COUNTA((x,y,z))))),1)*100,)

Mine:

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)

#### jeffreyweir

##### Active Member
Okay, about time I unveiled my formula:

=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)))