List rows Left to Right into single column

[RJK]

I have a need to list cells (F3:Q16), see attached file, into a single column (E21:E33) but WITHOUT sorting, just in the order they are in the matrix, starting with row1 and going left to right.

Any help or ideas would be very much appreciated.

Regards,
JC

[CAntosh]

Try the formula below, array-entered (confirm with Ctrl + Shift + Enter instead of Enter) into E21, then fill down through E42:

=IFERROR(INDEX(RNG,INT(SMALL(IF(RNG<>"",ROW(RNG)+(0.01*COLUMN(RNG))),ROW(1:1))-2),100*(SMALL(IF(RNG<>"",ROW(RNG)+(0.01*COLUMN(RNG))),ROW(1:1))-TRUNC(SMALL(IF(RNG<>"",ROW(RNG)+(0.01*COLUMN(RNG))),ROW(1:1))))-5),"")

It should automatically update as your "Frames" data is changed. See the attachment for assistance:

[FlameRetired]

Withdrawn by FR. Wrong thread.

[Phuocam]

One way...

=IFERROR(INDIRECT(TEXT(SMALL(IF(RNG<>"",ROW(RNG)*10000+COLUMN(RNG)),ROWS($E$21:E21)),"\R000000\C0000"),0),"")

Enter with Ctrl+Shift+Enter.

[FlameRetired]

This solution uses a helper column. In column F of the attached array enter this formula in F21 and fill down until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then enter this regular formula in E21 and fill down until you get blanks.

Please Login or Register  to view this content.

[Pete_UK]

This approach does not use an array formula, but uses two simple helper columns. Put this formula in cell R3:

=COUNTA(F3:Q3)

and copy it down to the bottom of your data (R16) - it counts the number of entries for each row. Then put zero in cell S2 and this formula in S3:

=IF(R3=0,"",R3+S2)

and copy this down to S16 - this gives a cumulative sum of those counts.

Then you can use this formula in cell E21 (I put it in G21 in the attached file):

=IF(ROWS($1:1)>MAX(S:S),"",INDEX(RNG,MATCH(ROWS($1:1)-1,$S$2:$S$16),ROWS($1:1)-INDEX($S$2:$S$16,MATCH(ROWS($1:1)-1,$S$2:$S$16))))

and copy this down as far as you need to.

Hope this helps.

Pete