# Non-macro sort

#### bellawala

##### Board Regular
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

##### MrExcel MVP
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
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

##### MrExcel MVP
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
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

Replies
0
Views
372
Replies
31
Views
1K
Replies
16
Views
197
Replies
4
Views
193
Replies
38
Views
564

1,147,451
Messages
5,741,188
Members
423,647
Latest member
lyanndominique

### 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.

### Which adblocker are you using?

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

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