Automatic sorting without empty cells

  • Thread starter Thread starter Euh
  • Start date Start date
E

Euh

Hello

I have data like that

11
21
13

12
9

3

4
12

34
2

And I would like to have a columm that gives me

11
21
13
12
9
3
4
12
34
2

This needs to be done automatically so that each time a new data is
entered in the original column, the formatted column is changed
(without empty cells)
 
Euh,

Let's say your original data is in A1:A100. In cell A101, enter a space. In
the first cell of where you want your other list, enter the array
formula(all on one line, using Ctrl-Shift-Enter):

=INDEX(A:A,SMALL(IF(NOT(ISBLANK($A$1:$A$100)),ROW($A$1:$A$100),ROW($A$101)),
ROW(A1)),1)

Copy down 100 rows, and you're done.

The cells that don't return values will return the value in A101, which
since you entered a space, will neaten up your list.

HTH,
Bernie
MS Excel MVP
 
Back
Top