Non-macro sort

[bellawala]

In its simplest form, my problem is to sort a given numeric list from one column to the next without using macros. Imagine you have 10 numbers in random order in one column and you would like those to be sorted in the next as values in the first column are changing.

thank you.

 

[Aladin Akyurek]

On 2002-10-31 13:46, bellawala wrote:
In its simplest form, my problem is to sort a given numeric list from one column to the next without using macros. Imagine you have 10 numbers in random order in one column and you would like those to be sorted in the next as values in the first column are changing.

thank you.

Let A2:A11 house ten numbers that we want sort in ascending order in another location.

In B2 enter & copy down:

=RANK(A2,$A$2:$A$11,1)+COUNTIF($A$2:A2,A2)-1

In C2 enter & copy down:

=INDEX($A$2:$A$11,MATCH(ROW(1:1),$B$2:$B$11,0))

The C-range shows the desired sorting that adapts itself to changes in the source range.

You can also use the following single formula that uses a function from the morefunc.xll add-in...

In D2 enter & copy down:

=INDEX(VSORT($A$2:$A$11,,1),ROW(1:1))

 

[Legacy 11779]

Alternatively, J Walkenbach offers the following array formula ("Oh no! Not an inefficient array Formula!") which only takes one column.

Select B2:B11 and enter(Ctrl+Shift+Enter) :-

=SMALL(A2:A11,(ROW(INDIRECT("1:" & ROWS(A2:A11)))))

 

[Ekim]

Put your unsorted numbers in say column A and name them MyRange. Then, in say column B, enter the following formula (enter the formula with a simple Enter i.e. not array entered):

=SMALL(MyRange,ROW()-(IF(ISERROR(ROW(OFFSET(MyRange,-1,0))),0,ROW(OFFSET(MyRange,-1,0)))))

For a descending sort, use LARGE.

When you change your numbers in column A, the sorted numbers in column B will automatically adjust.

The formula was found on the Net.

Regards,

Mike

 

[Aladin Akyurek]

It's up to you to choose...

We have 100 numbers in A in no paricular order. These numbers must be sorted (in ascending order) in an adjacent column by using formulas.


Method 1. SortNumsWithRank

Formula(s) involved:

=RANK(A2,$A$2:$A$101,1)+COUNTIF($A$2:A2,A2)-1

=INDEX($A$2:$A$101,MATCH(ROW(1:1),$B$2:$B$101,0))

Method 2. SortNumsWithVsort

=INDEX(VSORT($A$2:$A$101,,1),ROW(1:1))

Method 3. SortNumsWithSmallArray

{=SMALL($A$2:$A$101,(ROW(INDIRECT("1:" & ROWS($A$2:$A$101)))))}

This method uses an array-formula.

Method 4. SortNumsWithSmallOffset

=SMALL($A$2:$A$101,ROW()-(IF(ISERROR(ROW(OFFSET($A$2:$A$101,-1,0))),0,ROW(OFFSET($A$2:$A$101,-1,0)))))



What follows shows the efficiency profile of each method...

AllProfiles.xls
	A	B	C	D	E	F	G	H	I	J	K
2
3		WorkBook Summary
4		Cell Counts				Calc Time(Millisec)		MicroSecs	Volatility	Book	Bytes
5		UsedRange	%Waste	Constants	Formulae	ReCalc	FullCalc	perFormula	%	OvHead	per Cell
6	SortNumsWithRank	300	0.0%	100	200	0.09	17.03	85.14	0.6%	10.16	54.24
7	SortNumsWithVsort	200	0.0%	100	100	0.19	29.35	293.54	0.6%	14.39	282.14
8	SortNumsWithSmallArray	200	0.0%	100	100	7.13	19.39	193.91	36.8%	11.60	36.40
9	SortNumsWithSmallOffset	200	0.0%	100	100	7.67	20.28	202.85	37.8%	11.74	36.40
10
Sheet1

This message was edited by Aladin Akyurek on 2002-11-02 04:55