Flipping columns of data upside down?

[sophie marksson]

How can I reverse/flip a column (or columns) of data upside down? For
example if I have a column of three rows;

1
2
3

and I want to change it to;

3
2
1

Is there any basic tool within Excel for doing this? or do I have to
write some sort of function? The colums of data I am using are big
(more than 1000 rows).

Merry Xmas to all.

[J.E. McGimpsey]

If your data is sorted, just select your column and use Data/Sort and
choose Descending.

If the data is not already sorted, use a different (adjacent is
easiest) column to fill the series down the new column (1 to N, where N
is the number of rows in the original column). Now select both columns
and sort on the new one, using Descending again. Then delete the new
column.

In article <4e0b396c.01122...@posting.google.com>, sophie
marksson <soph...@mail.com> wrote:

--
Email address ROT13 encoded. Decode for real address.

[Murthy]

Sophie,

Create a new column with the label "Number" and down below, fill up the
column with numbers 1, 2, 3. For doing this easily, you can enter 1 and 2
in the first two cells, select both the cells and drag down the fill handle
to the extent required.

Then select the entire data, including labels, go to Data>Sort>Select
"Number" under Sort By drop-down box and select Descending radio button.
Click on OK.

Now you got the data reversed, isn't it? Now, delete the newly created
column "Number".
--
Regards,
Murthy

"sophie marksson" <soph...@mail.com> wrote in message
news:4e0b396c.01122...@posting.google.com...

[David McRitchie]

If you have to do reverse to this many times and are not adverse to a
macro solution see (not recommended for formulas)
topic: Reverse Cells in Selection end for end
http://www.mvps.org/dmcritchie/excel/join.htm#reversi

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote ...

[sophie marksson]

Thanks for taking the time to reply/help. But I think you might have
misunderstood my question. I'm not interested in sorting the values in
ascending/descending value or such like.

The data in the cells are of all sorts of values, e.g;

123
312.34
29.2
12.7
and so on

What I just want to do is literally flip the columns upside down, so
in the case of the example numbers above I would get;

12.7
29.2
312.34
123

It seems to be such a trivail thing to do, but I just cant find any
easy way of doing it (i.e theres no built in standard tool for doing
this)

Thanks

[Gord Dibben]

Sophie

Have another look at Murthy's solution. You do not sort your existing values.
You sort the new "Number" column. Your existing values just get turned upside
down as you wish.

HTH Gord Dibben Excel MVP - XL97 SR2

[Peo Sjoblom]

Sophie,

did you try the solutions J.E. and Murphy provided? They work,
if the adjacent column to your values is already occupied, select it and
do insert>columns and you should have a new empty column to the right
of the values you want to "reverse" Assume the reverse column is A
in B1 put 1, in B2 2, fill down as long as needed, select both columns, do
data>sort,
and sort by column B in descending order, now column A should
be "reversed". Select column B and delete it..

--
Regards,

Peo Sjoblom

"sophie marksson" <soph...@mail.com> wrote in message
news:4e0b396c.01122...@posting.google.com...

[Tushar Mehta]

[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to sophie marksson.
Please use the newsgroup for further discussion.]

The solution you got from Murthy and J.E. do work. However, if you'd
rather not do a sort of any kind or if you want a dynamic solution
(i.e., one that adapts to changing data), use one of those below. To
understand them, look up the various functions in XL Help (or check the
'Array Formulas' page on my site, which also includes a link to Chip
Pearson's site where you will find more on the subject).

Assume your range is named aRng (see below for how to do that). For my
testing I used aRng =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

Option 1: If you want the reversed values in D, starting with D6, array-
enter, in D6, =OFFSET(aRng,ROWS(aRng)-(ROW()-ROW($D$6))-1,0). To figure
out how to array-enter a formula see below. Now, copy down the formula
to get as many cells as you need.

This formula is easier to understand than the next one. However, note
that the formula contains the address of the first result cell.
Consequently, the formula must be adjusted to match where it is entered
(or moved).

Option 2: This is a more general solution that works irrespective of
what aRng is and where the result appears (or is moved). Select as many
contiguous cells in a column as there are original data values (or more,
but not less). Enter the array-formula
=N(OFFSET(aRng,COUNTA(aRng)-(ROW(aRng)-ROW(OFFSET(aRng,0,0,1,1)))-1,0))

This formula might be easier to understand with the foll.
simplification. Assume the original data start in row 1. Now, the
formula simplifies to =N(OFFSET(aRng,COUNTA(aRng)-ROW(aRng),0))

The above tested with XL2002.

--
aRng refers to the range of cells (Insert | Name > Define...) that
contains your cell numbers. It could be a dynamic reference that would
cover all entries in column A. Something like
aRng =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, *XL* will display the formula enclosed within
curly brackets { and }.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <4e0b396c.01122...@posting.google.com>, sophie marksson
<soph...@mail.com> wrote

[jmess...@usgs.gov]

You might try sorting in decending order.
Jane

>.
>