• 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 012 - Turn a mixed 2-d array into a 1-d array.

So while you guys are at it, I might as well throw in a shorter one....


=IFERROR(MATCH("f",N(OFFSET(A,(ROW(OFFSET(A1,,,COUNTA(-A),))-1)/COLUMNS(A),MOD(ROW(OFFSET(A1,,,COUNTA(-A),))-1,COLUMNS(A)))),0),MATCH("f",T(OFFSET(A,(ROW(OFFSET(A1,,,COUNTA(-A),))-1)/COLUMNS(A),MOD(ROW(OFFSET(A1,,,COUNTA(-A),))-1,COLUMNS(A)))),0))


Of course you didn't think I was going to conjure up something new, did you! Yes, still the same logic, but at least 247 is closer to sanity... :)
 
Sam: that negative in front of the COUNTA to coerce blank cells to a value of zero is genius!


What's cool about this formula is that you can also use it on an array like so:

=IFERROR(MATCH({"f",1,"a"}....
 
Here's the comparison between a formula that returns a row vector and a column vector:


=OFFSET(a,MOD(ROW(OFFSET(A1,,,COUNTA(-a)))-1,ROWS(a)),INT(ROW(OFFSET(A1,,,COUNTA(-a),))/(ROWS(a)+1)),1,1)


=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(-a)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(-a)))-1,COLUMNS(a)),1,1)
 
Turns out there was a bug in my formula that didn't rear its head on the sample data, but did on a wider or longer dataset. The issue was this bit:

INT(ROW(OFFSET(A1,,,COUNTA(-a),))/(ROWS(a)+1))

...which needs to be revised like so:

INT(ROW(OFFSET(A1,,,COUNTA(a)))/ROWS(a)-1/ROWS(a))


So here's my revised comparison between a formula that returns a row vector and a column vector:

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


=OFFSET(a,MOD(ROW(OFFSET(A1,,,COUNTA(-a)))-1,ROWS(a)),INT(ROW(OFFSET(A1,,,COUNTA(a)))/ROWS(a)-1/ROWS(a)),1,1)
 
Resurrecting conversation with newer Excel functions!

If you can find a character that definitely does not exist in a, such as "♦":
=TEXTSPLIT(TEXTJOIN("♦",TRUE,a),,"♦",TRUE)

- Sergey
 
Back
Top