Sorting in reverse (from right-to-left and from top-to-bottom)

Hello,
Below you can find an example of my worksheet (A1:A9):

DC03210
FE65520
BD92940
CT84170
GT99280
SE38734
FT01484
DR04567
RD31798

In this example, you see the sort as I want it (right-to-left and
top-to-bottom). to be more specific, I want to have it sorted that you first
see all the 'numbers' which end on 0, after that, 1 etc.But how can I let
Excel do this sort? Now I'm first sorting all the numbers by hand.
(yes, this is the 2nd time I board this message, but first I wasn's that
specific, that's why!)

Thanx (again)!

Roger

Excel can only use a maximum of 3 criteria to do a sort. Even if you split
the numbers into different columns, you will have the 3 column restriction
to contend with.The best you wil get, is to do a sort on the last 3 digits of
each number. Digits 4 - 7 will however remain unsorted.

"retman" wrote:

> Hello,
> Below you can find an example of my worksheet (A1:A9):
>
> DC03210
> FE65520
> BD92940
> CT84170
> GT99280
> SE38734
> FT01484
> DR04567
> RD31798
>
> In this example, you see the sort as I want it (right-to-left and
> top-to-bottom). to be more specific, I want to have it sorted that you first
> see all the 'numbers' which end on 0, after that, 1 etc.But how can I let
> Excel do this sort? Now I'm first sorting all the numbers by hand.
> (yes, this is the 2nd time I board this message, but first I wasn's that
> specific, that's why!)
>
> Thanx (again)!
>
> Roger

Then again, the sort will not work from right-to-left (although I selected
this on the 'allignment'-page).
After doing what you suggest, the result is:
origineel
CT84170
DC03210
GT99280
FT01484
FE65520
DR04567
SE38734
RD31798
BD92940
(sorting on the last 3 digits). And I want the result as I mentioned before.


"Kassie" wrote:

> Excel can only use a maximum of 3 criteria to do a sort. Even if you split
> the numbers into different columns, you will have the 3 column restriction
> to contend with.The best you wil get, is to do a sort on the last 3 digits of
> each number. Digits 4 - 7 will however remain unsorted.
>
> "retman" wrote:
>
> > Hello,
> > Below you can find an example of my worksheet (A1:A9):
> >
> > DC03210
> > FE65520
> > BD92940
> > CT84170
> > GT99280
> > SE38734
> > FT01484
> > DR04567
> > RD31798
> >
> > In this example, you see the sort as I want it (right-to-left and
> > top-to-bottom). to be more specific, I want to have it sorted that you first
> > see all the 'numbers' which end on 0, after that, 1 etc.But how can I let
> > Excel do this sort? Now I'm first sorting all the numbers by hand.
> > (yes, this is the 2nd time I board this message, but first I wasn's that
> > specific, that's why!)
> >
> > Thanx (again)!
> >
> > Roger

On Wed, 30 Mar 2005 22:15:03 -0800, retman <[email protected]>
wrote:

>Hello,
>Below you can find an example of my worksheet (A1:A9):
>
>DC03210
>FE65520
>BD92940
>CT84170
>GT99280
>SE38734
>FT01484
>DR04567
>RD31798
>
>In this example, you see the sort as I want it (right-to-left and
>top-to-bottom). to be more specific, I want to have it sorted that you first
>see all the 'numbers' which end on 0, after that, 1 etc.But how can I let
>Excel do this sort? Now I'm first sorting all the numbers by hand.
>(yes, this is the 2nd time I board this message, but first I wasn's that
>specific, that's why!)
>
>Thanx (again)!
>
>Roger

I think the simplest method would be to use a VBA macro to reverse the string;
sort in place; then reverse the string again to get back to your original.

You can do the sort within VBA, but the routine to do that would be much
slower.

In earlier versions of Excel, you may need to replace the StrReverse function
in the macro. There is information at the Microsoft Knowledge Base to do this.

To enter the macro, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that appears.

To use this macro, first SELECT the region you wish to sort.
<alt-F8> opens the Macro Dialog box. Select the appropriate macro and RUN

======================================
Sub SortReverse()
Dim c As Range

For Each c In Selection
c.Value = StrReverse(c.Value)
Next c

Selection.Sort _
Key1:=Selection.Cells(1, 1)

For Each c In Selection
c.Value = StrReverse(c.Value)
Next c

End Sub
=================================

You may need to make some modifications depending on your precise setup, but
this should, at least, get you started.


--ron

Hi Ron,

This is exactly what I was looking for! Thanx a 1000x!
And now I have to by myself "VBA for Dummies"....!

Cheers,

Roger

"Ron Rosenfeld" wrote:

> On Wed, 30 Mar 2005 22:15:03 -0800, retman <[email protected]>
> wrote:
>
> >Hello,
> >Below you can find an example of my worksheet (A1:A9):
> >
> >DC03210
> >FE65520
> >BD92940
> >CT84170
> >GT99280
> >SE38734
> >FT01484
> >DR04567
> >RD31798
> >
> >In this example, you see the sort as I want it (right-to-left and
> >top-to-bottom). to be more specific, I want to have it sorted that you first
> >see all the 'numbers' which end on 0, after that, 1 etc.But how can I let
> >Excel do this sort? Now I'm first sorting all the numbers by hand.
> >(yes, this is the 2nd time I board this message, but first I wasn's that
> >specific, that's why!)
> >
> >Thanx (again)!
> >
> >Roger
>
> I think the simplest method would be to use a VBA macro to reverse the string;
> sort in place; then reverse the string again to get back to your original.
>
> You can do the sort within VBA, but the routine to do that would be much
> slower.
>
> In earlier versions of Excel, you may need to replace the StrReverse function
> in the macro. There is information at the Microsoft Knowledge Base to do this.
>
> To enter the macro, <alt-F11> opens the VB Editor.
> Ensure your project is highlighted in the Project Explorer window, then
> Insert/Module and paste the code below into the window that appears.
>
> To use this macro, first SELECT the region you wish to sort.
> <alt-F8> opens the Macro Dialog box. Select the appropriate macro and RUN
>
> ======================================
> Sub SortReverse()
> Dim c As Range
>
> For Each c In Selection
> c.Value = StrReverse(c.Value)
> Next c
>
> Selection.Sort _
> Key1:=Selection.Cells(1, 1)
>
> For Each c In Selection
> c.Value = StrReverse(c.Value)
> Next c
>
> End Sub
> =================================
>
> You may need to make some modifications depending on your precise setup, but
> this should, at least, get you started.
>
>
> --ron
>

On Thu, 31 Mar 2005 05:19:01 -0800, retman <[email protected]>
wrote:

>Hi Ron,
>
>This is exactly what I was looking for! Thanx a 1000x!
>And now I have to by myself "VBA for Dummies"....!
>
>Cheers,
>
>Roger

Roger,

Glad to help. Post back if you run into problems.

Best,

--ron

>-----Original Message-----
>Hello,
>Below you can find an example of my worksheet (A1:A9):
>
>DC03210
>FE65520
>BD92940
>CT84170
>GT99280
>SE38734
>FT01484
>DR04567
>RD31798
>I have a spreadsheet solution, if you give me your email
address I will send it to you.
>In this example, you see the sort as I want it (right-to-
left and
>top-to-bottom). to be more specific, I want to have it
sorted that you first
>see all the 'numbers' which end on 0, after that, 1
etc.But how can I let
>Excel do this sort? Now I'm first sorting all the numbers
by hand.
>(yes, this is the 2nd time I board this message, but
first I wasn's that
>specific, that's why!)
>
>Thanx (again)!
>
>Roger
>.
>