How do I sort a range of cells via a worksheet function

[RogerWilco]

I have one column of unsorted cells.

I want to create a second column with the sorted values from the first column.

I can easily copy/paste the first column and then manually use the sort menu command. But I want to do that via a worksheet function so that when I change the unsorted values in the first column, the sorted column automatically updates.

Make sense? I need to keep both the unsorted and sorted versions of the same data.

[Biff]

What kind of data is this for? Text? Numeric? Mixed? Any duplicates?

Biff

"RogerWilco" <RogerWilco.21efvq_1136863503.68@excelforum-nospam.com> wrote
in message news:RogerWilco.21efvq_1136863503.68@excelforum-nospam.com...
>
> I have one column of unsorted cells.
>
> I want to create a second column with the sorted values from the first
> column.
>
> I can easily copy/paste the first column and then manually use the sort
> menu command. But I want to do that via a worksheet function so that
> when I change the unsorted values in the first column, the sorted
> column automatically updates.
>
> Make sense? I need to keep both the unsorted and sorted versions of the
> same data.
>
>
> --
> RogerWilco
> ------------------------------------------------------------------------
> RogerWilco's Profile:
> http://www.excelforum.com/member.php...o&userid=18906
> View this thread: http://www.excelforum.com/showthread...hreadid=499625
>

[RogerWilco]

They are all numeric and there can be duplicates... Does that matter? Can't you do a sort regardless of data type?

[Biff]

To sort a range of cells that is all NUMERIC:

Assume the range is A1:5:

To sort ascending:

=SMALL(A$1:A$5,ROWS($1:1))

Copy down 5 cells.

To sort descending just replace SMALL with LARGE.

To sort a range of cells that is all TEXT:

To sort ascending:

Entered as an array using the key combo of CTRL,SHIF,ENTER:

=INDEX($A$1:$A$5,MATCH(SMALL(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),ROWS($1:1)),COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),0))

Copy down 5 cells.

To sort descending replace SMALL with LARGE.

Don't even ask to sort mixed data!!!! (both TEXT and NUMERIC)

Biff

"RogerWilco" <RogerWilco.21h9yq_1136995812.5831@excelforum-nospam.com> wrote
in message news:RogerWilco.21h9yq_1136995812.5831@excelforum-nospam.com...
>
> They are all numeric and there can be duplicates... Does that matter?
> Can't you do a sort regardless of data type?
>
>
> --
> RogerWilco
> ------------------------------------------------------------------------
> RogerWilco's Profile:
> http://www.excelforum.com/member.php...o&userid=18906
> View this thread: http://www.excelforum.com/showthread...hreadid=499625
>