Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Sorting a six digit number by terminal digit

Howdy All,

I have record information in a spreadsheet that contains terminal digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers, example 12
34 56. But the system works kind of in reverse, 56 is the first number you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Sorting a six digit number by terminal digit


I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian



--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=570746

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Sorting a six digit number by terminal digit

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian



--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=570746



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default Sorting a six digit number by terminal digit

In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian



--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=570746




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Sorting a six digit number by terminal digit

Thanks Duke.

I was trying to avoid that.

Wondering if there is anyway to sort data in place?


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=570746








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Sorting a six digit number by terminal digit

However, *before* you sort, you must remove the formulas and leave the data
behind.

Select the column of "revised" numbers, and right click in the selection and
choose "Copy".
Right click again and choose "Paste Special".
Click on "Values", then <OK, then <Esc.

Now you can sort.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian



--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=570746






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Sorting a six digit number by terminal digit

Rag,

Why do you need to do that?


"RagDyeR" wrote in message
...
However, *before* you sort, you must remove the formulas and leave the
data
behind.

Select the column of "revised" numbers, and right click in the selection
and
choose "Copy".
Right click again and choose "Paste Special".
Click on "Values", then <OK, then <Esc.

Now you can sort.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=570746








  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,290
Default Sorting a six digit number by terminal digit

Brian,
You would have to use a macro to do that.
It would place reversed numbers in an adjoining column.
Sort the data using the new column data and then clear the column.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"Brian"

wrote in message
Thanks Duke.
I was trying to avoid that.
Wondering if there is anyway to sort data in place?


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default Sorting a six digit number by terminal digit

Can't see any way to do what you'd like to do, except by creating a helper
column with a formula akin to what I offered.

Sorry

"Brian" wrote:

Thanks Duke.

I was trying to avoid that.

Wondering if there is anyway to sort data in place?


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=570746







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Sorting a six digit number by terminal digit

You actually *don't* ... if your sorting the original data together with the
"helper" column.

I should have stated that if you wanted to retain the original data in it's
initial configuration, and just sort the "helper" column, formula removal
must be done first.

Sorry for the confusion.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Brian" wrote in message
...
Rag,

Why do you need to do that?


"RagDyeR" wrote in message
...
However, *before* you sort, you must remove the formulas and leave the
data
behind.

Select the column of "revised" numbers, and right click in the selection
and
choose "Copy".
Right click again and choose "Paste Special".
Click on "Values", then <OK, then <Esc.

Now you can sort.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2, and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


--
starguy


------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=570746











  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Sorting a six digit number by terminal digit

Thanks, that's what I ended up doing.


"Duke Carey" wrote in message
...
Can't see any way to do what you'd like to do, except by creating a helper
column with a formula akin to what I offered.

Sorry

"Brian" wrote:

Thanks Duke.

I was trying to avoid that.

Wondering if there is anyway to sort data in place?


"Duke Carey" wrote in message
...
In an adjacent cell use this formula - (assuming the number is in A2)

=right(A2,2)&mid(A2,3,2)&left(A2,2)

Copy the formula down & then sort on that column


"Brian" wrote:

The data is actually all together, ie 123456. NOT 12 34 56.

Thanks.


"starguy" wrote
in
message ...

I am not sure that either you have space after two digits or not.
with spaces digits would be like this 12 34 56
without spaces digits would be 123456

confirm that what type of data you have because method would be
different for both type of digits.

Brian Wrote:
Howdy All,

I have record information in a spreadsheet that contains terminal
digit
record numbers.

Terminal digit is a filing system that uses 3 sets of 2 numbers,
example 12
34 56. But the system works kind of in reverse, 56 is the first
number
you
look at, then 34, then 12.

I have these record numbers in column B. I need to sort them by
terminal
digit, meaning by the last 2 numbers first, then by the middle 2,
and
lastly
by the first 2.


Can someone help me out with a simple solution?

Thanks,
Brian


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=570746









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
how to input a 12 digit number into a cell jedi New Users to Excel 11 June 19th 06 08:26 PM
How do i save a 12 digit number in a .csv document in excell? darin van houten Excel Discussion (Misc queries) 2 March 27th 06 11:31 PM
Display a 16 digit number GrlsWthCrls Excel Discussion (Misc queries) 2 January 6th 06 08:10 PM
Letters and 000s in front of actual number not sorting correctly darkjedi Excel Discussion (Misc queries) 1 December 1st 05 04:48 PM
When we enter a 16 digit number (credit card) the last digit chan. ceking Excel Discussion (Misc queries) 5 December 9th 04 12:45 AM


All times are GMT +1. The time now is 05:31 PM.

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

About Us

"It's about Microsoft Excel"