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

#### Sam Mathai Chacko

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### New Member
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

##### Well-Known Member
Resurrecting conversation with newer Excel functions!
=TOROW(a)

Maintains the data type too…

Last edited: