How to take a list of cells and sort them into a new list

[Guest]

Let's say I have a list of 20 student numbers, unsorted, in cells A3:A22.
How, if possible, can I set up my worksheet so that it will automatically
take the values in that range of cells, sort them, and display them in cells
C3:C22 in sorted order?

Thanks for any help.

 

[Max]

Let's say I have a list of 20 student numbers, unsorted, in cells A3:A22.
How, if possible, can I set up my worksheet so that it will automatically
take the values in that range of cells, sort them, and display them in
cells
C3:C22 in sorted order?

One way to set it up using non-array formulas ..

Auto-sort in ascending order

Put in C3:
=INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0))

Put in D3:
=IF(A3="","",A3+ROW()/10^10)
(Leave D12 empty)

Select C33, copy down to D22 (Hide away col D)

C3:C22 returns the full ascending sort of A3:A22. Tied values, if any, will
be returned in the same relative order as they appear in the source

--------

Auto-sort in descending order

Put in C3:
=INDEX(A:A,MATCH(LARGE(D,ROW(A1)),D,0))

(similar to above, except using LARGE)

Put in D3:
=IF(A3="","",A3-ROW()/10^10)
(Leave D12 empty)

(similar to above, except for the change of "+" to "-")

Select C33, copy down to D22. (Hide away col D)

C3:C22 returns the full descending sort of A3:A22. Tied values, if any, will
be returned in the same relative order as they appear in the source.

---

 

[Max]

If there's the possibility of blank cells within A3:A22, we can use these
expressions instead in col C to gather a neat output in C3:C22 (no ugly
#NUMs!)

Auto-sort in ascending order
Put in C3, copy down:
=IF(ROW(A1)>COUNT(D),"",INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0)))

Auto-sort in descending order
Put in C3, copy down:
=IF(ROW(A1)>COUNT(D),"",INDEX(A:A,MATCH(LARGE(D,ROW(A1)),D,0)))

---

 

[Biff]

Why not just:

=SMALL(A$3:A$22,ROWS($1:1))

Biff

One way to set it up using non-array formulas ..

Auto-sort in ascending order

Put in C3:
=INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0))

Put in D3:
=IF(A3="","",A3+ROW()/10^10)
(Leave D12 empty)

Select C33, copy down to D22 (Hide away col D)

C3:C22 returns the full ascending sort of A3:A22. Tied values, if any,
will be returned in the same relative order as they appear in the source

--------

Auto-sort in descending order

Put in C3:
=INDEX(A:A,MATCH(LARGE(D,ROW(A1)),D,0))

(similar to above, except using LARGE)

Put in D3:
=IF(A3="","",A3-ROW()/10^10)
(Leave D12 empty)

(similar to above, except for the change of "+" to "-")

Select C33, copy down to D22. (Hide away col D)

C3:C22 returns the full descending sort of A3:A22. Tied values, if any,
will be returned in the same relative order as they appear in the source.

 

[Max]

Why not just:
=SMALL(A$3:A$22,ROWS($1:1))

Now, why didn't I think of that <g>
Looks good enough for the post

MYin:
Pl dismiss my suggestion totally. Go with Biff's above.
You didn't state how you wanted it auto-sorted in your post
If you need it in descending order, just replace SMALL with LARGE

---

 

[Guest]

Hello Biff and Max,

Thank you very much for the advice. The SMALL command worked fine, although
there was the remaining problem of #NUM with empty cells, but that's OK.
Thanks again for your time and response.

MYin

 

[Biff]

Try this one:

=IF(COUNT(A$3:A$22)<=ROWS($1:1),SMALL(A$3:A$22,ROWS($1:1)),"")

Biff