SOLVED

Problem with pivot tables and zip codes

%3CLINGO-SUB%20id%3D%22lingo-sub-2662730%22%20slang%3D%22en-US%22%3EProblem%20with%20pivot%20tables%20and%20zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662730%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%201200%20zip%20codes%20which%20I%20want%20to%20organize%20with%20a%20pivot%20table.%26nbsp%3B%20I%20have%20reformatted%20the%20list%20as%20%22text%22%20and%20as%20%22special%20(zip%20code)%22%20multiple%20times%2C%20spot-checking%20to%20be%20sure%20all%20numbers%20are%20formatted%20the%20same%20way.%26nbsp%3B%20No%20matter%20what%20I%20do%2C%20when%20I%20create%20the%20pivot%20table%2C%20SOME%20of%20the%20zips%20are%20read%20as%20if%20they%20are%20numbers.%26nbsp%3B%20So%20I%20get%20duplicates%20of%20many%20of%20the%20zips%2C%20with%20separate%20counts%2C%20in%20the%20final%20pivot.%20(too%20many%20to%20fix%20by%20hand).%20I%20have%20copied%20and%20pasted%20the%20list%20of%20zips%20to%20a%20new%20worksheet%3B%20removed%20the%20pivot%20table%20and%20re-made%20it%3B%20refreshed%20multiple%20times%3B%20reformatted%20the%20list%20as%20%22text%22%20and%20as%20%22special%22%20(zip%20codes).%26nbsp%3B%20Nothing%20works.%26nbsp%3B%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2662730%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2663493%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20pivot%20tables%20and%20zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2663493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1131761%22%20target%3D%22_blank%22%3E%40KM_Date%3C%2FA%3E%26nbsp%3BDifficult%20to%20diagnose%20without%20seeing%20the%20ZIP%20codes.%20If%20they%20are%20just%20formatted%20as%20text%20it%20could%20be%20that%20the%20%22duplicates%22%20contain%20trailing%20spaces.%20For%20instance%20a%20ZIP%20code%20like%20%2212345-1234%22%20is%20NOT%20equal%20to%20%2212345-1234%20%22%20and%20will%20be%20seen%20as%20two%20separate%20items.%20Or%20perhaps%20you%20copied%20them%20from%20a%20web-site.%20Then%20they%20could%20contain%20hidden%20characters%20with%20a%20similar%20effect%20as%20trailing%20spaces.%20Use%20TRIM%20and%20CLEAN%20functions%20to%20clean-up%20the%20data.%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20when%20these%20ZIP%20codes%20are%20recognised%20by%20Excel%20and%20can%20be%20custom%20formatted%2C%20e.g.%20as%20a%20US%20Zip%20code%2C%20the%20trailing%20spaces%20etc.%20should%20have%20no%20impact.%20Can%20you%20upload%20a%20file%20with%20just%20the%20list%20of1200%20ZIP%20codes%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665578%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20pivot%20tables%20and%20zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20so%20much%20for%20your%20offer%20to%20help%20out.%26nbsp%3B%20Here%20is%20the%20file.%26nbsp%3B%20These%20were%20hand%20collected%20by%20merchants%20on%20a%20small%20town%20main%20street%2C%20and%20I%20entered%20the%20data%20myself%20from%20their%20hand-written%20sheets.%26nbsp%3B%20The%20colored%20bands%20were%20used%20to%20check%20each%20column%20of%20zips%20as%20it%20was%20entered.%26nbsp%3B%20As%20you%20can%20see%20the%20pivot%20is%20not%20doing%20what%20it%20is%20supposed%20to%20do%3B%20and%20the%20original%20table%20is%20all%20in%20text%20format.%26nbsp%3B%20Any%20suggestions%20most%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665583%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20pivot%20tables%20and%20zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665583%22%20slang%3D%22en-US%22%3EThis%20is%20not%20the%20first%20or%20last%20time%20I%20will%20have%20a%20list%20like%20this%2C%20so%20I%20am%20hoping%20to%20learn%20something%20here!%20Thank%20you%20in%20advance!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665727%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20pivot%20tables%20and%20zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665727%22%20slang%3D%22en-US%22%3EThanks%40riny_van_eekelen.%20If%20you%20look%20at%20the%20pivot%20table%2C%20it%20has%20all%20the%20zips%20in%20numerical%20order...%20then%20further%20down%20the%20list%2C%20the%20zips%20start%20up%20again%20in%20the%201000's%2C%20etc.%20I%20have%20duplicates%20of%20many%20of%20the%20zips.%20It's%20like%20there%20are%20two%20lists%2C%20but%20the%20counts%20are%20different%2C%20so%20clearly%20they%20are%20each%20referring%20to%20different%20groups%20of%20zips.%20How%20are%20they%20different%20if%20they%20are%20all%20formatted%20as%20%22text%22%20in%20the%20original%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I have a list of 1200 zip codes which I want to organize with a pivot table.  I have reformatted the list as "text" and as "special (zip code)" multiple times, spot-checking to be sure all numbers are formatted the same way.  No matter what I do, when I create the pivot table, SOME of the zips are read as if they are numbers.  So I get duplicates of many of the zips, with separate counts, in the final pivot. (too many to fix by hand). I have copied and pasted the list of zips to a new worksheet; removed the pivot table and re-made it; refreshed multiple times; reformatted the list as "text" and as "special" (zip codes).  Nothing works.  Any suggestions?

 

Thank you!

10 Replies

@KM_Date Difficult to diagnose without seeing the ZIP codes. If they are just formatted as text it could be that the "duplicates" contain trailing spaces. For instance a ZIP code like "12345-1234" is NOT equal to "12345-1234 " and will be seen as two separate items. Or perhaps you copied them from a web-site. Then they could contain hidden characters with a similar effect as trailing spaces. Use TRIM and CLEAN functions to clean-up the data. 

However, when these ZIP codes are recognised by Excel and can be custom formatted, e.g. as a US Zip code, the trailing spaces etc. should have no impact. Can you upload a file with just the list of1200 ZIP codes?

@Riny_van_Eekelen Thanks so much for your offer to help out.  Here is the file.  These were hand collected by merchants on a small town main street, and I entered the data myself from their hand-written sheets.  The colored bands were used to check each column of zips as it was entered.  As you can see the pivot is not doing what it is supposed to do; and the original table is all in text format.  Any suggestions most appreciated!

This is not the first or last time I will have a list like this, so I am hoping to learn something here! Thank you in advance!

@KM_Date You need to help me a little bit. What's the pivot table not doing? Seems OK to me. So please indicate what's wrong with it.

Thanks@riny_van_eekelen. If you look at the pivot table, it has all the zips in numerical order... then further down the list, the zips start up again in the 1000's, etc. I have duplicates of many of the zips. It's like there are two lists, but the counts are different, so clearly they are each referring to different groups of zips. How are they different if they are all formatted as "text" in the original?
best response confirmed by KM_Date (Occasional Contributor)
Solution

@KM_Date Sorry, now I see!

 

See attached. I copied your list and ran it through Text-to-columns and made Text of it all. Now in Column E and the pivot table beside it. I guess this is what you want.

@KM_Date 

If creating PivotTable add data to data model it sorts correctly

image.png

Thank you Riny_van_Eekelen. Can you tell me how "copying the list and running it through text-to-columns" differs from highlighting the list and changing the format to "text"? What should I do differently next time this happens? Also, can you tell me the steps you went through to "run it through text to columns"?

@KM_Date If you format the column you intend to enter the ZIP codes in as Text before you manually enter the codes it should work. But entering a mix of numbers and text first and then format the column as text will not work. Excel merely left justifies what you entered, numbers and texts.

 

On the Data ribbon you'll find the icon for Text to columns. In step 3 of 3 you can indicate that you want to treat the data as text. That transforms all data a type of text. That's just the way it is.

 

And I'm afraid that the "data model" option is not for you as you indicated that you ar on a Mac.

 

Thank you, this all makes sense. I'll do it that way next time! Thank you for your help!