Forum Discussion
Problem with pivot tables and zip codes
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 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 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.
