Sorting a range into another range

[James C]

Hello all.

I have a range A1:A2
these two cells contain formulas
each formula outputs a numeric value
I want to have range B1:B2 that contains the values, but sorted, descending.
Can someone propose formulas for B1, B2 that does this?

Thanks!

J

[martindwilson]

only two cells?
b1=(max(a1:a2)
b2= min(a1:a2)

[Pete_UK]

Put this in B1:

=LARGE($A$1:$A$2,ROWS($1:1))

Then copy the formula into B2 - the second term will become ROWS($1:2).

Hope this helps.

Pete

[James C]

Ah, elegant solution Pete, but, alas, I didnt draft my example properly, its going to be utilized for 3 or more cells
J

[Pete_UK]

I expected it would do. Just change the range to suit how much data you have in column A, e.g.:

=LARGE($A$1:$A$100,ROWS($1:1))

then copy down.

Hope this helps.

Pete

[James C]

NICE!
And what do I change in the formula IF I want to change from ascending to descending?
J

[Pete_UK]

Use SMALL instead of LARGE.

Hope this helps.

Pete

[James C]

PERFECT!
THanks.

[Pete_UK]

You're welcome - thanks for feeding back.

How about spreading a few "stars" around for the people who help you?

Pete

[flemingkr]

I have a similar challenge, and getting nowhere. In my case the range is two columns, the first of which is text and the 2nd is number. Data looks like:


A 5
A 10
C 5
D 10
A 18
D 5
D 24

This range is intended for data entry by a person, and I prevent duplicate rows but have no control over order. To do an INDEX() and MATCH() on this data, I need the numeric values for any given alpha value to be descending. So, the desired copy of this range would look like:


C 5
D 24
D 10
D 5
A 10
A 5


I've shown the rows for any given text value to be contiguous, but that doesn't matter--but all rows for 'D' must be sorted desc on the numeric value, all rows for 'A' sorted desc on the corresponding number, etc.

The ONLY reason I think I need to do this is to satisfy the range criteria for INDEX(), where, for a given text string (e.g., 'A'), I need to find the smallest number that's >= to some criteria number I give it.
What I think would be a 1 minute problem in SQL has taken me all afternoon and I still can't 'get it'. Suggestions?

I see this as creating/maintaining a sorted 'copy' of a range, where the sort instructions are 'first on column 1 then on column 2 desc', but that viewpoint is keeping me from getting anywhere.