Invert a column

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thank you very much. I don't think the problem of blanks showing as zeros is
crucial.

By the way, your suggestion of using the special format hides both real
zeros and zeros-as-blanks

 

[Guest]

Try this:

=IF(INDEX(DATA,ROWS(DATA)-ROWS($1:1)+1)=0,"",INDEX(DATA,ROWS(DATA)-ROWS($1:1)+1))

 

[Guest]

I should note that I was assuming the range in column A was changing often so
did not hardcode the column A range reference into the formula. If your data
set is very large, I am sure you will have problems w/calculation speed. In
which case you should consider Teethless Mama's suggestion (I reposted it
below):

=IF(INDEX(DATA,ROWS(DATA)-ROWS($1:1)+1)="","",INDEX(DATA,ROWS(DATA)-ROWS($1:1)+1))

 

[Ragdyer]

This formula is a little shorter.
Enter it *anywhere*, and copy down 10 rows:

=INDEX($A$1:$A$10,ROWS(1:$10))

Revise to match your actual range!