you could try this in B1 and copy down as far as needed

=INDEX(A:A,LOOKUP(2,1/($A$1:$A$65535<""),ROW($A$1:$A$65535))-ROWS(B$1:B1)+1)

although it will return a 0 for empty cells (which can be hidden by using a
custom format, such as General;General;;General).



"Jakobshavn Isbrae" wrote:

I need to invert the data in a column. The column is a mixture of text
numbers and blanks. Here is a small example in col A

a1:
a2: ssn
a3: 123
a4: 45
a5: 6789
a6:
a7:
a8: tel
a9: 189xx
a10: 276xx

What I need in col B

b1: 276xx
b2: 189xx
b3: tel
b4:
b5:
b6: 6789
b7: 45
b8: 123
b9: ssn
b10:

I am now doing this manually by first copying col A to col B, and then I put
in a col of numbers like 1,2,3,4,€¦, and then I sort by this new col in
descending order.

I have to repeat this every time col A is changed.

Is there a formula I can put in col B which could figure out where the last
col A value is and to automatically do this for me?

Thanks in advance for any help.

--
jake