Invert a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jakobshavn Isbrae - 12 Jun 2007 17:09 GMT

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.

Signature

jake

Reply to this Message

JMB - 12 Jun 2007 18:24 GMT

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

> 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
[quoted text clipped - 33 lines]
>
> Thanks in advance for any help.

Reply to this Message

Jakobshavn Isbrae - 12 Jun 2007 18:53 GMT

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

Signature

jake

> you could try this in B1 and copy down as far as needed
>
[quoted text clipped - 40 lines]
> >
> > Thanks in advance for any help.

Reply to this Message

JMB - 12 Jun 2007 23:40 GMT

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

> Thank you very much. I don't think the problem of blanks showing as zeros is
> crucial.
[quoted text clipped - 46 lines]
> > >
> > > Thanks in advance for any help.

Reply to this Message

Ragdyer - 13 Jun 2007 01:30 GMT

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!

Signature

HTH,

RD

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

> Thank you very much. I don't think the problem of blanks showing as zeros
> is
[quoted text clipped - 50 lines]
>> >
>> > Thanks in advance for any help.

Reply to this Message

Teethless mama - 12 Jun 2007 19:38 GMT

Try this:

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

> 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
[quoted text clipped - 33 lines]
>
> Thanks in advance for any help.

Reply to this Message