Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 6th 09, 05:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default 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.

  #2   Report Post  
Old July 6th 09, 05:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Sort by last two characters, not the first.


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

  #3   Report Post  
Old July 6th 09, 05:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Sort by last two characters, not the first.

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.

  #4   Report Post  
Old July 6th 09, 05:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,942
Default Sort by last two characters, not the first.

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.

  #5   Report Post  
Old July 6th 09, 05:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default Sort by last two characters, not the first.

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.




  #6   Report Post  
Old July 7th 09, 04:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
Default Sort by last two characters, not the first.

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.

DataText to ColumnsDelimited byOtherHyphenNext.

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't enter characters, is some sort of auto-correct on? TomCon via OfficeKB.com Excel Discussion (Misc queries) 1 April 21st 09 06:54 AM
Separating characters from a cell so I can sort on them Princess Caroline Excel Worksheet Functions 6 June 9th 08 08:36 PM
In Excel find characters when multiple characters exist w/i a cel teacher-deburg Excel Worksheet Functions 1 December 5th 05 11:22 PM
How do I sort in Excel by the no. of characters in a cell? yasmingeo Excel Worksheet Functions 3 October 21st 05 12:29 PM
Sort order for characters & symbols - does anything follow z? Neil Goldwasser Excel Discussion (Misc queries) 4 August 5th 05 05:02 AM


All times are GMT +1. The time now is 09:49 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017