Turn array with empty fields into a consecutive column

@ > Home > Contents > Turn array with empty fields into a consecutive column Excel 97+

Task:

Turn the following array into a column with no empty cells.

        A        B        C        D        E        F    
  1 Array                                         Vector       
  2        1        2                 3                 1      
  3                 4       50                          2      
  4                                                     3      
  5        6        7       80        9                 4      
  6       10                         11                50      
  7                12                                   6      
  8                                                     7      
  9                                                    80      
 10                                                     9      
 11                                                    10      
 12                                                    11      
 13                                                    12      
Example for the task

Solution (Klaus "Perry" Pago, 2007/06/20):

F2: =INDEX($A$2:$D$7,INT(
SMALL(IF($A$2:$D$7<>"",(ROW($1:$6)-1)*6+COLUMN(A:D)),ROW(A1))/6)+1,MOD(
SMALL(IF($A$2:$D$7<>"",(ROW($1:$6)-1)*6+COLUMN(A:D)),ROW(A1)),6))

- fill it down in column F.
- it is an array formula (enter it by using the pressed keys CTRL-SHIFT-ENTER, otherwise it does not work)

Redesigned for general use:

1. Assign a name (f.e. "array") to the array.

2. Select F2. Insert Name Define "Place" referring to
=SMALL(IF(array<>"",(ROW(INDIRECT("1:"&ROWS(array)))-1)*ROWS(array)+
COLUMN(INDIRECT(SUBSTITUTE("A:"&ADDRESS(1,COLUMNS(array),4),"1",)))),ROW(1:1))

which is the general formula for
SMALL(IF($A$2:$D$7<>"",(ROW($1:$6)-1)*6+COLUMN(A:D)),ROW(A1)),6))

3. Use now F2: {=INDEX(array,Place/ROWS(array)+1,MOD(Place,ROWS(array)))}

You can now move or resize your array as you want.

Note: Excel xl2003- allows 8 nested functions only. That means, that the following formula will not work there (but perhaps in xl2007, saving you work from step 1 to 3):

{=INDEX(array,
SMALL(IF(array<>"",(ROW(INDIRECT("1:"&ROWS(array)))-1)*ROWS(array)+
COLUMN(INDIRECT(SUBSTITUTE("A:"&ADDRESS(1,COLUMNS(array),4),"1",)))),ROW(1:1))
/ROWS(array)+1,MOD(
SMALL(IF(array<>"",(ROW(INDIRECT("1:"&ROWS(array)))-1)*ROWS(array)+
COLUMN(INDIRECT(SUBSTITUTE("A:"&ADDRESS(1,COLUMNS(array),4),"1",)))),ROW(1:1))
,ROWS(array)))}