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

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

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)+

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)+