Sorting Data
 

There formulae are helpful, but the text-and-numbers one breaks down if
you're sorting a string where the first part is either a negative or positive
number -- it sorts the data set by the absolute value of the number, in
descending order.

Any fixes?

Henrik

"Ragdyer" wrote:

Harlan has posted a couple of formulas that will auto sort in *helper*
columns, and there's an old SMALL() formula that will do the same for
numbers, auto sorting in a "helper" column.

Numbers *only*:
=SMALL($A$1:$A$100,ROW())

Harlan's array formulas:

Text Only *OR* Numbers Only:
=INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10," <"&$D$1:$D$10),ROW()-ROW($
E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0))

Text *AND* Numbers:
=INDEX($A$1:$A$10,MATCH(SMALL(COUNTIF($A$1:$A$10," <"&$A$1:$A$10)+COUNT($A$1:
$A$10)*ISTEXT($A$1:$A$10),ROW()-ROW($B$1)+1),COUNTIF($A$1:$A$10,"<"&$A$1:$A$
10)+COUNT($A$1:$A$10)*ISTEXT($A$1:$A$10),0))

Array formulas must be entered with CSE (<Ctrl <Shift <Enter).
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Forness" <Dave wrote in message
...
How can I configure the sort function so if I insert data in the cells out
of
sequence, it will automatically be in sequence the next time it is opened?