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

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

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


"JMB" wrote:

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

Try this:

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


"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

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



"Jakobshavn Isbrae" wrote:

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


"JMB" wrote:

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

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!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jakobshavn Isbrae" wrote in
message ...
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
--
jake


"JMB" wrote:

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