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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MYin - 12 Sep 2006 02:06 GMT

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.

Reply to this Message

Max - 12 Sep 2006 02:43 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 12 Sep 2006 02:54 GMT

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)))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Biff - 12 Sep 2006 04:14 GMT

Why not just:

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

Biff

>> 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
[quoted text clipped - 37 lines]
> 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.

Reply to this Message

Max - 12 Sep 2006 07:32 GMT

> 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

MYin - 12 Sep 2006 17:48 GMT

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

> > Why not just:
> > =SMALL(A$3:A$22,ROWS($1:1))
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Biff - 12 Sep 2006 19:48 GMT

Try this one:

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

Biff

> Hello Biff and Max,
>
[quoted text clipped - 15 lines]
>> 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

Reply to this Message