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

Groups > comp.sys.mac.apps > #17800

Re: Any way to get Excel to sort these numbers properly?

Show all headers | View raw

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

Back to comp.sys.mac.apps | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web