Sorting a six digit number by terminal digit

[Brian]

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]

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.

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

[Brian]

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

Thanks.


"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Duke Carey]

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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[Brian]

Thanks Duke.

I was trying to avoid that.

Wondering if there is anyway to sort data in place?


"Duke Carey" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>>

[RagDyeR]

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[Brian]

Rag,

Why do you need to do that?


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>>
>
>

[Jim Cone]

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"
<[email protected]>
wrote in message
Thanks Duke.
I was trying to avoid that.
Wondering if there is anyway to sort data in place?

[Duke Carey]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

[RagDyeR]

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" <[email protected]> wrote in message
news:%[email protected]...
Rag,

Why do you need to do that?


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>>
>
>

[Brian]

Thanks, that's what I ended up doing.


"Duke Carey" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>