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]

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