Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.

[Sam Mathai Chacko]

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

 

[jeffreyweir]

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"}....

 

[jeffreyweir]

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)

 

[jeffreyweir]

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)

 

[SERANDLYA]

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

 

[p45cal]

Resurrecting conversation with newer Excel functions!

=TOROW(a)

Maintains the data type too…

 

[Peter Bartholomew]

How things change!
A couple of years or so ago, one might have had

= LET(
    m, ROWS(array),
    n, COLUMNS(array),
    k, SEQUENCE(m*n,,0),
    r, 1+QUOTIENT(+k,n),
    c, 1+MOD(k, n),
    INDEX(array, r, c)
  )

Now, with array shaping functions,

= TOCOL(array)

will do the job!

What has changed is the function library certainly but, more than that, the entire thought process by which one builds solutions is changed. Or not, if you exploit backward compatibility to stick with legacy methods!