sorting more than 3 columns

[saybut]

Hi,

Does anyone know if its possible to sort data by more than the 3 criteria in the sort menu in Data>Sort?

Preferably I need to do it on 5 columns, I can't figure it out though - Would I need to use a pivot table?

Thanks for any help at all.

Regards,

saybut.

[saybut]

Hi, sorry I've just figured out how to do it.

Just in case anyone is interested, if you're sorting five columns, A,B,C,D,E

sort the last three first, C,D,E and then sort A,B.


or if anyone has any better ways let me know.

thanks.

[Bob Phillips]

No, that is the way to do it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"saybut" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, sorry I've just figured out how to do it.
>
> Just in case anyone is interested, if you're sorting five columns,
> A,B,C,D,E
>
> sort the last three first, C,D,E and then sort A,B.
>
>
> or if anyone has any better ways let me know.
>
> thanks.
>
>
> --
> saybut
> ------------------------------------------------------------------------
> saybut's Profile:
http://www.excelforum.com/member.php...fo&userid=5949
> View this thread: http://www.excelforum.com/showthread...hreadid=514329
>

[Pete_UK]

Another way is to join columns together in a helper column, eg in F2
enter the formula:

=A2&B2&C2&D2 &E2

copy down, and sort on this one field. The fields should be converted
to fixed width if they are not already. If the field is text then
spaces can be added to make it up to a fixed number of characters, and
if it is numeric it can be made fixed width using TEXT( ). So you might
actually have something like:

=A2&B2&REPT(" ",20-LEN(B2))&C2&REPT(" ",10-LEN(C2))&TEXT(D2,"000")&E2

Here A2 is already fixed width (eg code number), B2 is made up to 20
characters, C2 is made up to 10 characters, D2 is a number fixed to 3
characters and E2 is not changed. The advantage is only one sort is
needed.

Of course, this leaves two other slots in the sort dialogue box for
further refinement.

Hope this helps.

Pete

[saybut]

Thanks a lot for the posts. At the moment I'll just stick with the orignal method. Although it doesn't seem to work sort from the back to the front more than twice - maybe just me though.

thanks again.