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

M

MYin

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

Max

MYin said:
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?
Click to expand...

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

Auto-sort in ascending order

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

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

Select C3:D3, 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:D,ROW(A1)),D:D,0))

(similar to above, except using LARGE)

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

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

Select C3:D3, 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.
 
M

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:D),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

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

Biff

Why not just:

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

Biff

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

Auto-sort in ascending order

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

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

Select C3:D3, 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:D,ROW(A1)),D:D,0))

(similar to above, except using LARGE)

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

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

Select C3:D3, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
Click to expand...
 
M

Max

Biff said:
Why not just:
=SMALL(A$3:A$22,ROWS($1:1))
Click to expand...

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
 
M

MYin

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
 
Top