How do i sort a range and place the results in another range without using Macros?

I have a worksheet that has a table of data.
The table spans cells B2 - Q72

I would like to place the same table of data underneath it but sorted
by one of the colums in Ascending Order, then the same again
underneath that but this time sorted in Descending order.

eg:

|
|Table of Data
|
|
|Table sorted in Ascending Order
|
|
|Table sorted in Descending Order
|



so when data is changed in the top table the sort orders in the tables
underneath are reflected as well.

I would like this to happen automatically when data is entered and not
by manually running a macro to update all the time?

Is this possible?

Do the following:

you have a total of 71 rows, and so select column B rows say 74 to 74+71=145 and enter the following array formula
=PERCENTILE($B$2:$B$72,(MATCH(B2:B72,$B$2:$B$72,0)-1)/(COUNT($B$2:$B$72)-1))
and press control + shift + enter

The column 1 values are now sorted in ascending order.

Do the same after leaving 1 row and enter the following for descending:
=PERCENTILE($B$2:$B$72,1-(MATCH(B2:B72,$B$2:$B$72,0)-1)/(COUNT($B$2:$B$72)-1))

As for the remaining columns, you can use the vlookup as follows:
for the second column
=VLOOKUP(B74,$B$2:$Q$72,2,FALSE)
for the third column
=VLOOKUP(B74,$B$2:$Q$72,3,FALSE)
and so on...

- Mangesh

Douglas,

You need to insert formulas in column A that ranks the values. You can use
RANK if you have numeric values, or a formula like this in cell A2, copied
down to A3:A72:

=SUMPRODUCT(($B$2:$B$72<=B2)*1)

to rank based on column B. If you can have ties, you need to add something,
like

=SUMPRODUCT(($B$2:$B$72<=B2)*1) + ROW()/1000

and then do a numeric RANK on that value, in yet another column.

Then, to create your auto-sorting tables, use a VLOOKUP keyed to rank
numbers (from 1 to 72 for the first table, and 72 to 1 for the second table)
in column A. Let's say that the top left cell of your first auto-sorted
table is B80. Use this formula, and copy to B80:Q151:

=VLOOKUP($A80,$A$2:$Q$72,COLUMN(),FALSE)

Then make your second table, and you're done.

If you can't get it to work, post back and I will send you a working
version.

HTH,
Bernie
MS Excel MVP

"Douglas" <dougsdir24@yahoo.com> wrote in message
news:befb84ec.0501280155.c8d9c7c@posting.google.com...
> I have a worksheet that has a table of data.
> The table spans cells B2 - Q72
>
> I would like to place the same table of data underneath it but sorted
> by one of the colums in Ascending Order, then the same again
> underneath that but this time sorted in Descending order.
>
> eg:
>
> |
> |Table of Data
> |
> |
> |Table sorted in Ascending Order
> |
> |
> |Table sorted in Descending Order
> |
>
>
>
> so when data is changed in the top table the sort orders in the tables
> underneath are reflected as well.
>
> I would like this to happen automatically when data is entered and not
> by manually running a macro to update all the time?
>
> Is this possible?