sort by zip problem

[ElSteveO]

I have a list in excel with customer name, address, city state, zip. I want to sort the list alphabetically and by zip. Alphabetically works fine, but sorting by zip doesn't.

Basically it's sorting my one list as two sorts in one list, as if half the list is sorted, then it sorts the other half. The problem is that I only have one list. I may have combined two lists initially, but it should still sort by zip.

Those cells are formatted as Number -> Special -> Zip code.

Thank you,
steve

 

[SkipVought]

Hi,

You know that you can sort using Data/Sort ... and then select the 2 fields that you want to sort on.

It ALL happens in ONE SORT operation.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ElSteveO]

Of course I sorted that way. My point is it wouldn't sort the zips properly. The second half of the zips were in order but not combined with the first half. I went through all the zips in the second half, retyped the zips, and resorted. This time it worked. But I've had this problem before. I think there's a bug in office 2000. Thanks for your response.
Steve

 

[SkipVought]

no bug at all.

Strings sort differently than numbers. If you have a STRING of 5 digits and a 5-digit number, they do NOT sort as you expect.

You can set up a simple test.

make 10 zips.

sort them

make the FIRST zip a STRING by editing and placing a [tic] in the first position.

resort.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[EndoMan]

Also, related to the "String" theory, if some of the Zips are 9-digit (12345-6789) and some are 5-digit (12345), the 9-digit will sort as text and the 5-digit as numbers, regardless of your cell formatting.

 

[ElSteveO]

Thanks for the help, guys. Here is a small example of my zip sort:

60609
60618
60620
60646
60657
60668
60805
60609
60618
60634
60657
60804

See how it crescendos at 60805 then seems to start over? And the 60609 is representative of two different companies, yet they don't sort together. Again, it worked fine after I retyped the 2nd half zips (the one's after 60805). But I've seen this problem before. I think it's because I've imported zips from more than one list. I think it's a bug b/c all the zips are formatted consistently as zips.

Thanks again for your assistance. I hope we can solve this b/c there have to be many people who can use this info.

Steve

 

[SkipVought]

The first 7 are NUMBERS

The last 5 are STRING

I can get them to sort EXACTLY as you have listed them.

Solution: Make ALL zips STRINGS.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

2nd that - your data must've come from 2 different sources because you have 2 different data types there...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ElSteveO]

I think it must've come from 2 different sources, but once it was all on the same worksheet I formatted ALL of them to be zips (format -> special -> zip). So they should sort properly since they're all formatted the same. But they didn't until I retyped all the zips after the highest one in the middle. My list has several hundred names, so I wasn't too pleased to do all the extra work.

I still say it's a bug. The data types are the same.

 

[xlbo]

You don't understand data types then

Formatting does not change a data type - it only changes what you SEE

The underlying data does not change and excel does not sort based on formatting

To change the data type you must physically either coerce it into a value, either using the VALUE() formula or the "Times 1 fix" OR change to text by using the TEXT() formula

If you use either of the formulae, you can then copy and paste Special > values to keep the data as per the formula

Sorry you had to do all that work manually - you could've fixed it in 30 seconds - but it most certainly is not a bug - the same principles apply to pretty much ALL applications

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ElSteveO]

Geoff, that's very enlightening. I assume that zips are text, so I can I apply the text formula to all the contacts at once?

 

[xlbo]

absolutely - if you apply this formula:

=TEXT(A1,"0")

where A1 is the 1st cell that contains a ZIP code, and copy it down your data-set, you can then copy , paste special > values and then use this new data to replace the old set of zip codes - it should then sort fine

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ElSteveO]

Brilliant! I did it and it works. Thanks very much.
Steve