Sorting Excel list 2

[jfhewitt]

My wife has a rather long mailing list for address labels sorted by postal zip codes. Whenever new records are added, I cannot sort them into the list unless I copy the zip code column to a new column, with "paste special" and "Value" selected. Does anyone know why this is necessary, and whether there is a way to sort zips without creating a new column? Thanks, Excel gurus.

 

[SkipVought]

Hi,

I believe that this is an issue of the difference between NUMBERS and STRINGS of NUMERIC CHARACTERS.

Are you using one of the the Special Zip Formats in Format/Cells/Number Tab?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jfhewitt]

I have used numbers, text and custom zip code. Doesn't seem to matter...I need to paste special to a new column to sort.

 

[SkipVought]

Please explain what you mean. When you state, "I have used numbers, text and custom zip code." are you formatting the entire column with that format?

What zip codes are NOT sorting as you expect?

Do you have ANY 5+4 zip codes?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jfhewitt]

I am formatting the entire column. When I go to data/sort, the newly added records do not fall into zip code order. If I copy the entire column to a new column with paste special/values, it works. Even tho I change the format of the column, it won't do a complete sort until I copy the entire column as stated.

 

[Dreamboat]

Sounds like you need "the old data fix"

copy any empty cell
select your bad data
hit edit-paste special-add-ok

try sorting again.

Excel VBA Certification is Finally Here!

http://www.vbaexpress.com/forum

 

[SkipVought]

j,

You answered one question out of 3.

Also, how are you adding new records?

Do ALL the added records not sort properly?



Just trying to get as much info to help.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Madawc]

You could always copy the data to a Word table and then sort that table. I've done it with mixed data and it worked fine.

Madawc Williams (East Anglia)

 

[jfhewitt]

Skip:
She is adding the new records by direct entry, or by using data/form. All of the new records do not sort properly. Keep in mind that I am able to sort, but only after the "data fix" as described by Dreamboat. I just want to know the cause of the problem. I have both the old and new column formatted for text or zip code (custom).

 

[SkipVought]

Can you send a copy of this workbook? skipandmary1017 at mindspring.com

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jfhewitt]

No need to send copy...I am able to sort the list. I just would like to know why it is so difficult. If I format a column as text or zip, it would seem to me the list short sort on zip with no problem. Just confused, that's all. Thanks.

 

[SkipVought]

Well, if you don't want to take me up on the offer.....

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jfhewitt]

I appreciate your offer, but the list is sorted, the labels printed, and wife happy. You have been very helpful.

 

[01Patrik]

Then give the man a shiny, pointy thingy... that's the sole reason they exist.

// Patrik

 

[Dreamboat]

Patrik: Is it bigger than a breadbox?



Excel VBA Certification is Finally Here!

http://www.vbaexpress.com/forum