Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.sys.mac.apps > #17799 > unrolled thread

Any way to get Excel to sort these numbers properly?

Back to article view | Back to comp.sys.mac.apps

Contents

  Any way to get Excel to sort these numbers properly? patty1@sonic.net (Patty Winter) - 2013-09-28 22:35 +0000
    Re: Any way to get Excel to sort these numbers properly? Davoud <star@sky.net> - 2013-09-28 19:13 -0400
      Re: Any way to get Excel to sort these numbers properly? Patty Winter <patty1@wintertime.com> - 2013-09-29 00:14 +0000
        Re: Any way to get Excel to sort these numbers properly? Davoud <star@sky.net> - 2013-09-29 11:30 -0400
    Re: Any way to get Excel to sort these numbers properly? Lewis <g.kreme@gmail.com.dontsendmecopies> - 2013-09-29 01:56 +0000
      Re: Any way to get Excel to sort these numbers properly? Patty Winter <patty1@wintertime.com> - 2013-09-29 02:46 +0000
    Re: Any way to get Excel to sort these numbers properly? Mark Storkamp <mstorkamp@yahoo.com> - 2013-09-29 08:49 -0500
      Re: Any way to get Excel to sort these numbers properly? PhillipJones <pjonescet@comcast.net> - 2013-09-29 12:05 -0400
        Re: Any way to get Excel to sort these numbers properly? Patty Winter <patty1@wintertime.com> - 2013-09-30 04:40 +0000
          Re: Any way to get Excel to sort these numbers properly? PhillipJones <pjonescet@comcast.net> - 2013-09-30 14:13 -0400
          Re: Any way to get Excel to sort these numbers properly? Davoud <star@sky.net> - 2013-10-01 00:13 -0400
            Re: Any way to get Excel to sort these numbers properly? Patty Winter <patty1@wintertime.com> - 2013-10-01 05:14 +0000
      Re: Any way to get Excel to sort these numbers properly? Patty Winter <patty1@wintertime.com> - 2013-09-29 22:55 +0000

#17799 — Any way to get Excel to sort these numbers properly?

A cousin just called with a question about using Excel to
keep track of some postage-stamp collections. In some cases,
he has sets of stamps that have contiguous catalog numbers,
but other than the catalog number they're identical, so he
doesn't want to have to list each one separately. So, for
example, his catalog numbers might look like this:

   41200
   41201
   41202-41206
   41207
   41208
   ...

However, he isn't entering them in catalog order number,
so they aren't yet neatly sorted as shown above. He wants
to sort the resulting spreadsheet by catalog number, but
Excel is gagging on the cells that have a range of numbers.

I don't use Excel much, but a few minutes of experimentation
and Googling hasn't revealed a solution. The best I could 
accomplish was ending up with all the single-number fields
properly sorted, then the range field after those. 

I don't know how many range fields he has; if there aren't 
too many, maybe he could move them manually after sorting 
the others.  Does anyone know of a solution that would sort 
both types of fields properly? Is there any way to force 
Excel to only look at the number before the hyphen???


Patty

[toc] | [next] | [standalone]

#17800

Patty Winter:

> A cousin just called with a question about using Excel to
> keep track of some postage-stamp collections. In some cases,
> he has sets of stamps that have contiguous catalog numbers,
> but other than the catalog number they're identical, so he
> doesn't want to have to list each one separately. So, for
> example, his catalog numbers might look like this:
> 
>    41200
>    41201
>    41202-41206
>    41207
>    41208
>    ...
> 
> However, he isn't entering them in catalog order number,
> so they aren't yet neatly sorted as shown above. He wants
> to sort the resulting spreadsheet by catalog number, but
> Excel is gagging on the cells that have a range of numbers.
> 
> I don't use Excel much, but a few minutes of experimentation
> and Googling hasn't revealed a solution. The best I could 
> accomplish was ending up with all the single-number fields
> properly sorted, then the range field after those. 
> 
> I don't know how many range fields he has; if there aren't 
> too many, maybe he could move them manually after sorting 
> the others.  Does anyone know of a solution that would sort 
> both types of fields properly? Is there any way to force 
> Excel to only look at the number before the hyphen???

Yes. He can enter the numbers as text. He has only to type an
apostrophe in front of the numbers that do not include a range:

'41200
'41201
41202-41206
'41207
'41208
41209-41211
'41202
...

Excel will not show the apostrophe in the cell, but it will be visible
in the formula bar. Excel will automatically treat the ranges such as
41202-41206 as text. Then he can select the entire range of cells that
he wants to sort and select the catalog number column as the sorting
column (in ascending order, with "No Header Row" selected) and
everything will fall into place. After the sort, with all cells still
selected, there will be a yellow warning icon adjacent to the first
cell. He should click on that warning icon. The message will be "Number
Stored as Text," and there will be options below that message. He
should select the option "Error Checking Options" and in the dialog box
that opens /uncheck/ "Flag numbers formatted as text." This is quicker
and easier to do than it is to explain.

-- 
I agree with almost everything that you have said and almost everything that
you will say in your entire life.

usenet *at* davidillig dawt cawm

[toc] | [prev] | [next] | [standalone]

#17801

In article <280920131913132734%star@sky.net>, Davoud  <aaa@bbb.ccc> wrote:
>
>Yes. He can enter the numbers as text. He has only to type an
>apostrophe in front of the numbers that do not include a range:
>
>'41200
>'41201
>41202-41206
>'41207
>'41208
>41209-41211
>'41202
>...
>
>Excel will not show the apostrophe in the cell, but it will be visible
>in the formula bar. Excel will automatically treat the ranges such as
>41202-41206 as text. Then he can select the entire range of cells that
>he wants to sort and select the catalog number column as the sorting
>column (in ascending order, with "No Header Row" selected) and
>everything will fall into place. 

Okay, I don't know what I did wrong, but of the five cells that I
sorted, the one cell that has a range still ended up below the four
cells that have single numbers.


>After the sort, with all cells still
>selected, there will be a yellow warning icon adjacent to the first
>cell. He should click on that warning icon. The message will be "Number
>Stored as Text," and there will be options below that message. He
>should select the option "Error Checking Options" and in the dialog box
>that opens /uncheck/ "Flag numbers formatted as text." 

I did that and it got rid of the little green triangles in the cells,
but the one anomalistic number is still by itself at the bottom of
the list. :-(


Patty

[toc] | [prev] | [next] | [standalone]

#17816

Davoud:
> >Yes. He can enter the numbers as text. He has only to type an
> >apostrophe in front of the numbers that do not include a range:
> >
> >'41200
> >'41201
> >41202-41206
> >'41207
> >'41208
> >41209-41211
> >'41202
> >...
> >
> >Excel will not show the apostrophe in the cell, but it will be visible
> >in the formula bar. Excel will automatically treat the ranges such as
> >41202-41206 as text. Then he can select the entire range of cells that
> >he wants to sort and select the catalog number column as the sorting
> >column (in ascending order, with "No Header Row" selected) and
> >everything will fall into place. 

Patty Winter:
> Okay, I don't know what I did wrong, but of the five cells that I
> sorted, the one cell that has a range still ended up below the four
> cells that have single numbers.

Davoud:
> >After the sort, with all cells still
> >selected, there will be a yellow warning icon adjacent to the first
> >cell. He should click on that warning icon. The message will be "Number
> >Stored as Text," and there will be options below that message. He
> >should select the option "Error Checking Options" and in the dialog box
> >that opens /uncheck/ "Flag numbers formatted as text." 

Patty Winter:
> I did that and it got rid of the little green triangles in the cells,
> but the one anomalistic number is still by itself at the bottom of
> the list. :-(

I'm sorry that I can't help from afar. I have tried this repeatedly and
it works for me every time.

-- 
I agree with almost everything that you have said and almost everything that
you will say in your entire life.

usenet *at* davidillig dawt cawm

[toc] | [prev] | [next] | [standalone]

#17804

In message <524759b8$0$52746$742ec2ed@news.sonic.net> 
  Patty Winter <patty1@sonic.net> wrote:
> A cousin just called with a question about using Excel to
> keep track of some postage-stamp collections. In some cases,
> he has sets of stamps that have contiguous catalog numbers,
> but other than the catalog number they're identical, so he
> doesn't want to have to list each one separately. So, for
> example, his catalog numbers might look like this:

Format the column as text instead of numbers

-- 
Forgive your enemies, but remember their names.

[toc] | [prev] | [next] | [standalone]

#17806

In article <slrnl4f25u.nvq.g.kreme@mbp55.local>,
Lewis  <g.kreme@gmail.com.dontsendmecopies> wrote:
>
>Format the column as text instead of numbers

Thanks, but that was the first thing we tried.


Patty

[toc] | [prev] | [next] | [standalone]

#17815

In article <524759b8$0$52746$742ec2ed@news.sonic.net>,
 patty1@sonic.net (Patty Winter) wrote:

> A cousin just called with a question about using Excel to
> keep track of some postage-stamp collections. In some cases,
> he has sets of stamps that have contiguous catalog numbers,
> but other than the catalog number they're identical, so he
> doesn't want to have to list each one separately. So, for
> example, his catalog numbers might look like this:
> 
>    41200
>    41201
>    41202-41206
>    41207
>    41208
>    ...
> 
> However, he isn't entering them in catalog order number,
> so they aren't yet neatly sorted as shown above. He wants
> to sort the resulting spreadsheet by catalog number, but
> Excel is gagging on the cells that have a range of numbers.
> 
> I don't use Excel much, but a few minutes of experimentation
> and Googling hasn't revealed a solution. The best I could 
> accomplish was ending up with all the single-number fields
> properly sorted, then the range field after those. 
> 
> I don't know how many range fields he has; if there aren't 
> too many, maybe he could move them manually after sorting 
> the others.  Does anyone know of a solution that would sort 
> both types of fields properly? Is there any way to force 
> Excel to only look at the number before the hyphen???
> 
> 
> Patty

Two possible ways to do this:

1) Use two columns instead of 1, putting the start of the range in the 
first column and the end of the range into the second column, then sort 
on the first column.

2) Create a hidden column with a formula such as '=LEFT($A1,5)', then 
sort on the hidden column.

[toc] | [prev] | [next] | [standalone]

#17817

Mark Storkamp wrote:
> In article <524759b8$0$52746$742ec2ed@news.sonic.net>,
>   patty1@sonic.net (Patty Winter) wrote:
>
>> A cousin just called with a question about using Excel to
>> keep track of some postage-stamp collections. In some cases,
>> he has sets of stamps that have contiguous catalog numbers,
>> but other than the catalog number they're identical, so he
>> doesn't want to have to list each one separately. So, for
>> example, his catalog numbers might look like this:
>>
>>     41200
>>     41201
>>     41202-41206
>>     41207
>>     41208
>>     ...
>>
>> However, he isn't entering them in catalog order number,
>> so they aren't yet neatly sorted as shown above. He wants
>> to sort the resulting spreadsheet by catalog number, but
>> Excel is gagging on the cells that have a range of numbers.
>>
>> I don't use Excel much, but a few minutes of experimentation
>> and Googling hasn't revealed a solution. The best I could
>> accomplish was ending up with all the single-number fields
>> properly sorted, then the range field after those.
>>
>> I don't know how many range fields he has; if there aren't
>> too many, maybe he could move them manually after sorting
>> the others.  Does anyone know of a solution that would sort
>> both types of fields properly? Is there any way to force
>> Excel to only look at the number before the hyphen???
>>
>>
>> Patty
>
> Two possible ways to do this:
>
> 1) Use two columns instead of 1, putting the start of the range in the
> first column and the end of the range into the second column, then sort
> on the first column.
>
> 2) Create a hidden column with a formula such as '=LEFT($A1,5)', then
> sort on the hidden column.
>

As long as its individual numbers it will sort easily the complex number 
will not sort the way you want.  See this Movie I created: 
http://www.screencast.com/t/YGijcRL4d
It will time out in 30 days.

Note its based on Mac. I am sure PC is similar though names may be 
different and layout may look different.

-- 
Phillip M. Jones, C.E.T.      "If it's Fixed, Don't Break it"
http://www.phillipmjones.net    mailto:pjonescet@comcast.net

[toc] | [prev] | [next] | [standalone]

#17835

In article <l29j4l$v8l$1@news.albasani.net>,
PhillipJones  <pjonescet@comcast.net> wrote:
>
>As long as its individual numbers it will sort easily the complex number 
>will not sort the way you want.  See this Movie I created: 
>http://www.screencast.com/t/YGijcRL4d

Yes, that's exactly the problem I encountered when I did those
steps. The single numbers sorted fine, but the range ended up
at the bottom. :-(

Thank you for making that movie.


Patty

[toc] | [prev] | [next] | [standalone]

#17855

Patty Winter wrote:
> In article <l29j4l$v8l$1@news.albasani.net>,
> PhillipJones  <pjonescet@comcast.net> wrote:
>>
>> As long as its individual numbers it will sort easily the complex number
>> will not sort the way you want.  See this Movie I created:
>> http://www.screencast.com/t/YGijcRL4d
>
> Yes, that's exactly the problem I encountered when I did those
> steps. The single numbers sorted fine, but the range ended up
> at the bottom. :-(
>
> Thank you for making that movie.
>
>
> Patty
>
Now if there are multiple Complex numbers they will be sorted as well 
but will fall to the bottom after all the single or simple numbers.

-- 
Phillip M. Jones, C.E.T.      "If it's Fixed, Don't Break it"
http://www.phillipmjones.net    mailto:pjonescet@comcast.net

[toc] | [prev] | [next] | [standalone]

#17865

Patty Winter:
> Yes, that's exactly the problem I encountered when I did those
> steps. The single numbers sorted fine, but the range ended up
> at the bottom. :-(

One more time: enter the numbers as text and the sort /will/ work.

-- 
I agree with almost everything that you have said and almost everything that
you will say in your entire life.

usenet *at* davidillig dawt cawm

[toc] | [prev] | [next] | [standalone]

#17867

In article <011020130013235907%star@sky.net>, Davoud  <aaa@bbb.ccc> wrote:
>Patty Winter:
>> Yes, that's exactly the problem I encountered when I did those
>> steps. The single numbers sorted fine, but the range ended up
>> at the bottom. :-(
>
>One more time: enter the numbers as text and the sort /will/ work.

One more time: sorry, but that was the first thing we tried, even
before I posted my query to this group.


Patty

[toc] | [prev] | [next] | [standalone]

#17832

In article <mstorkamp-96677F.08491729092013@news.eternal-september.org>,
Mark Storkamp  <mstorkamp@yahoo.com> wrote:
>
>Two possible ways to do this:
>
>1) Use two columns instead of 1, putting the start of the range in the 
>first column and the end of the range into the second column, then sort 
>on the first column.

Ah, very clever!


>2) Create a hidden column with a formula such as '=LEFT($A1,5)', then 
>sort on the hidden column.

Does "($A1,5)" mean sort on the first five digits starting with
cell A1? I found out from him last night that his catalog numbers
actually only have four digits, so "($A1,4)" would do the trick,
right?


Patty

[toc] | [prev] | [standalone]

Back to top | Article view | comp.sys.mac.apps

csiph-web