• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
 
Hi Jeff,

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

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


-Sajan.
 
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.
 
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.
 
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!
 
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.
 
Here's my shot. Handles 3 horizontal arrays (any length), output is horizontal.

=INDEX((x,y,z),1,COLUMN(A1)-COUNTA(x)*(COLUMN(A1)>COUNTA(x))-COUNTA(y)*(COLUMN(A1)>COUNTA(x,y)),1+(COLUMN(A1)>COUNTA(x))+(COLUMN(A1)>COUNTA(x,y)))


Length: 146


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

http://chandoo.org/forums/topic/workday-with-non-adjacent-multi-holiday-ranges#post-21459
 
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.
 
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.
 
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)))>COUNTA(x))-COUNTA(y)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y)),

1+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x))+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y)))


for 276 characters...but it's not nearly as pretty. =(
 
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.
 
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.
 
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 :)
 
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)))>COUNTA(x))-COUNTA(y)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y)) is very very clever. I went a slightly different route.
 
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]
 
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 ;-)
 
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... <grin> But it's a good challenge, so back to the dark cavern of XL I go...
 
@Sajan

I like your trick of combining the row and column number with TEXT function and then substituting. Very clever.
 
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.
 
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.
 
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.
 
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)
 
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)))
 
Back
Top