Retrograde: reversing a row

[Schroeder70]

Hello everyone,

I would like to ask for help in writing a formula for reversing (retrograding) a row.

At the moment, I »paste special / transpose« the row into a column (B).
This column is next to a column numbered from 1 to 100 (A).
I selected the two columns together and then sort them in »descending« order according to column A.
Now, both columns are in reversed order.
I then select and copy the now reversed column B and »paste special / transpose« the column back into a row.
In this way I obtain a retrograded row, i.e. a row beginning with the initially last element and finishing with the formerly first element.

I would like to condense these working steps into a single action.
Ideally, I would like to paste a given row into ?sheet 1? (commencing in B1) and have the retrograded result appear in ?sheet 2? (also beginning in B1), although this is not necessary and any solution is welcome.

As a rule, my rows are between 10 and 100 elements (cells) in length.

If anyone could help I would very much appreciate it.
S

[Pete_UK]

With your data in A1:A100, put this formula in B1:

=INDEX($A$1:$A$100,101-ROWS($1:1))

then copy down.

If you want to copy into row 1 and see the reversed data in row 2, then put this formula in A2:

=INDEX($A$1:$A$100,101-COLUMN())

and copy across.

(There are lots of other variations).

Hope this helps.

Pete

[MarvinP]

Hi S,

Pete may have beat me to a good answer but see my formula on Sheet2.

[Richard Buttrey]

Hi,

How about in B1 on Sheet 2 copied across 100 cells

Please Login or Register  to view this content.

[Tony Valko]

Try this...

Let's assume you have this data on Sheet1 in the range A1:E1:

5, 4, 3, 2, 1

You want it to appear on Sheet2 in the range A1:E1 as:

1, 2, 3, 4, 5

Enter this formula on Sheet2 cell A1 and copy across to E1:

=INDEX(Sheet1!$A1:$E1,COLUMNS(Sheet1!A1:$E1))

[Schroeder70]

Hello everyone

Thank you very much for all your replies.
I tried every single one and they all work fine.
I am trying to learn from the varied approaches.

I have one question left which I think relates to Richard's formula.
If i write something into column A on sheet 1, the first cell of the reversed row on sheet 2 will show a zero.
It's not the end of the world but I wonder why that is.

[Tony Valko]

Use the specific data range in the COUNTA function:

=IF(COLUMNS($B1:B1)>COUNTA(Sheet1!$B1:$Z1),"",OFFSET(Sheet1!$B1,0,COUNTA(Sheet1!$B1:$Z1)-1-(COLUMNS($B1:B1)-1)))

[Schroeder70]

Thank you for your post, Tony.
Unfortunately, the number of elements per row is now curtailed to a maximum of 23.

[Tony Valko]

Use a larger end of range:

=IF(COLUMNS($B1:B1)>COUNTA(Sheet1!$B1:$IV1),"",OFFSET(Sheet1!$B1,0,COUNTA(Sheet1!$B1:$IV1)-1-(COLUMNS($B1:B1)-1)))

[Schroeder70]

Hello Tony
Thank you; this works.

[Schroeder70]

Hello Tony
Thank you; this works.

[Tony Valko]

Good deal. Thanks for the feedback!