|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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? |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com