Sort by last two characters, not the first.

I have a simple database; multiple rows and columns. I need to sort all the
data by info in one of the columns. I need to sort by the LAST TWO characters
of records in that column. Example of records:

R-BOILER-DA
R-FANCOILUNIT-QT
U-TRANSPAD-AN

All the data in the rows needs to stay together.

gino986;405318 Wrote:
> I have a simple database; multiple rows and columns. I need to sort all
> the
> data by info in one of the columns. I need to sort by the LAST TWO
> characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

Create a helper column that extract those last 2 letters with formula
=Right(A2,2) where A2 has first item to sort..

Then sort the data based on this new column.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113003

1. Select the column. From menu Data text to columns. with delmiter as
hyphen. will split this to three columns..
2. Now sort by the 3rd column
3. Combine the 3 columns using
=A1&"-"&B1&"-"&C1
4. Now copy> paste special> values to remove the formulas...

If this post helps click Yes
---------------
Jacob Skaria


"gino986" wrote:

> I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

hi
use a helper column along side of your data.
assuming the example data in in column A, in the helper column, enter this
=right(A2,2)
this will draw the last 2 characters of the data into the helper column
use the helper column as the primary sort.

regards
FSt1

"gino986" wrote:

> I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

Assuming your data starts on Row 2, put this formula...

=RIGHT(A2,2)

in an unused column, copy it down for the length of your data, then select
all the rows of data and sort it using the above column as the primary sort
column. You can then delete the above column's formulas when you are
through. If you think you might need to sort the data again some time, use
this formula instead...

=IF(A2="","",RIGHT(A2,2))

copy it down as far as you think you might ever have data in the future and
then hide the column (rather than delete its values) after the sort (so that
you can use it again in the future).

--
Rick (MVP - Excel)


"gino986" > wrote in message
...
>I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO
> characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

I know this post is a couple days late but thought I would post another
method without using formulas.

Copy the entire column so's you have two identicals.

Data>Text to Columns>Delimited by>Other>Hyphen>Next.

Select columns 1 and 2 and "Do not import(skip)"

Finish. Sort on that coumn.


Gord Dibben MS Excel MVP

On Mon, 6 Jul 2009 09:37:02 -0700, gino986
> wrote:

>I have a simple database; multiple rows and columns. I need to sort all the
>data by info in one of the columns. I need to sort by the LAST TWO characters
>of records in that column. Example of records:
>
>R-BOILER-DA
>R-FANCOILUNIT-QT
>U-TRANSPAD-AN
>
>All the data in the rows needs to stay together.