Sorting Data
 

hey, where do i input all this data?

"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?

You would nee helper columns, otherwise you would need an event macro

http://www.mvps.org/dmcritchie/excel/event.htm


--
Regards,

Peo Sjoblom


"Liz_Z" wrote in message
...
hey, where do i input all this data?

"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?

To demonstrate:

On a new sheet, enter this formula in B1:

=SMALL($A$1:$A$100,ROW())

And drag down to copy to say row 25.

Disregard the error messages.

This formula references Column A, so in Column A start entering
miscellaneous numbers in any row between 1 and 100 (formula boundaries).
As the numbers are entered, they will be displayed in Column B in numerical
order.

Column B is the "helper" column.

You could reference *any* column you wish in the formula, and you could
enter the formula in any other column besides the one you referenced in the
formula.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Liz_Z" wrote in message
...
hey, where do i input all this data?

"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?

Sorting Data
 

similar problem am trying to sort column D with many entries 1 thru 8. need
sort to group entries as 12345678 Can this be done with small formula. Am not
sure I follow all as to how to do this with a macro running it. All I can do
is blow it.
Help much appricated
Thanks

"Liz_Z" wrote:

hey, where do i input all this data?

"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?