Sorting Data

D

Dave Forness

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

Frank Kabel

Hi
this would require VBA (using an event procedure). Not something you
can configure

--
Regards
Frank Kabel
Frankfurt, Germany

Dave Forness said:
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
Click to expand...
opened?
 
D

dforness

Frank Kabel said:
Hi
this would require VBA (using an event procedure). Not something you
can configure

--
Regards
Frank Kabel
Frankfurt, Germany




Thank You Frank,
Click to expand...

If I understand correctly, new data which is not sequential when entered,
must be sorted each time to make it sequential. I'm probably mistaken but we
use Excel at work and I can't remember ever having to sort each time new data
is entered once the rows have been told how to sort. Could very well be
though that at work this situation has not presented itself.

Thanks Again,
Best Regards,
Dave Forness
 
M

Myrna Larson

Sorting doesn't automatically update. Either your memory is incorrect, the
situation hasn't come up, or there's VBA macro running behind the scenes that
re-sorts.
 
R

Ragdyer

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>).
 
D

dforness

Myrna Larson said:
Sorting doesn't automatically update. Either your memory is incorrect, the
situation hasn't come up, or there's VBA macro running behind the scenes that
re-sorts.



Thank You Myrna,
Click to expand...

I don't know what VBA is but will leave it there.

Best Regards,
Dave Forness
 
F

Frank Kabel

Hi RD
though these array formulas will sort automatically they have one
drawback: They're quite slow. So for more than 500 rows they're not
really practical (but this depends on OP's data).

FWIW an array formula for dealing also with error codes and boolean
values within the data range:
=INDEX(IF(ISBLANK($A$3:$A$20),"",$A$3:$A$20),MATCH(SMALL(COUNTIF(
$A$3:$A$20,"<"&$A$3:$A$20)+0*BigNumber*ISNUMBER($A$3:$A$20)+1*BigNumber
*ISTEXT($A$3:$A$20)+2*BigNumber*ISLOGICAL($A$3:$A$20)+3*BigNumber*ISERR
OR($A$3:$A$20)+4*BigNumber*ISBLANK($A$3:$A$20),ROW(1:1)),COUNTIF($A$3:$
A$20,"<"&$A$3:$A$20)+0*BigNumber*ISNUMBER($A$3:$A$20)+1*BigNumber*ISTEX
T($A$3:$A$20)+2*BigNumber*ISLOGICAL($A$3:$A$20)+3*BigNumber*ISERROR($A$
3:$A$20)+4*BigNumber*ISBLANK($A$3:$A$20),0))

Data range: A3:A20
Bignumber: defined name with =70000

and copy this formula down as far as needed
 
L

Liz_Z

hey, where do i input all this data?

Ragdyer said:
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>).
Click to expand...
 
R

RagDyeR

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!
==============================================



hey, where do i input all this data?
 
H

Henrik

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
 
C

Curt

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
 
Top