Non-macro sort

bellawala

Board Regular
Joined
Aug 20, 2002
Messages
73
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))
 
L

Legacy 11779

Guest
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEFGHIJK
2
3WorkBook Summary
4Cell CountsCalc Time(Millisec)MicroSecsVolatilityBookBytes
5UsedRange%WasteConstantsFormulaeReCalcFullCalcperFormula%OvHeadper Cell
6SortNumsWithRank3000.0%1002000.0917.0385.140.6%10.1654.24
7SortNumsWithVsort2000.0%1001000.1929.35293.540.6%14.39282.14
8SortNumsWithSmallArray2000.0%1001007.1319.39193.9136.8%11.6036.40
9SortNumsWithSmallOffset2000.0%1001007.6720.28202.8537.8%11.7436.40
10
Sheet1

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

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top