I have been pulling my hair out with this for over 8 hours this evening.
I have an excel sheet that has a column with user submitted zip codes. The data has been collected from attendees at an event. There are about 700 entries and many are from the same areas and there are many entries with the same zip codes.
I need to figure out how to find the top 15 zip codes that were submitted. Can anyone help with this?
Thank you in advance.
Hi,
Insert a pivot table. Drag the column containing the zip codes into the row label box, and into the values box.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
You can use a pivot table.
Drag the Postal code column to row area and data area (use count of)... then right-click row data and select Field Settings, then click Advanced and choose Top 10 Autoshow and change 10 to 15 in the drop down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thank you so much for the reply, I am not understanding what you mean by that though.Originally Posted by sweep
See here for Pivot table Intro:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and then here for the specifics to what you need...
http://www.contextures.com/xlPivot05.html
(Show Top Items section)
Thank you for this too. I do not know what "row area" and "data area" are though.
See my previous post for somewhere to look for answers.
I think I am getting somewhere but still confused.
I am in the pivot table view. Everything that I brought in was in 1 column, just zip codes. I selected that as my field and then made that my row labels. I am still unsure of what to do next.
I also had this table create a new sheet for this one column. Is that OK?
Did you also drag that to Data Area (and made sure it was Count of Zip Codes)?
After that, click in any row label, then right click and select Field Settings.
Click Advanced and show Show Top Ten.. then change the 10 in the drop down menu to 15.
If you want, post a sample workbook of what you've got... (no confidentiall stuff).
Here it is. Pretty small and simple.
Thank you so much for your help. This place is a great resource!
Last edited by NBVC; 06-22-2010 at 08:36 AM. Reason: Removed confidential workbook
See attached.
I removed columns A & B and deleted your original attachment.
That looks like confidential info.
OK...this is perfect. How did you get the "Count Of" field?
After you drag the Zip code field to the Sigma Values section, it might say Sum Of Zip Codes... just click that and select Value Field Settings, then select Count.
That simply returns a sinlge number for me. ...738
Not sure what that means?
Repost the attachment which shows the problem....
It only allows me to pull a field in 1 box and then i change to count it just gives me the count of rows.
Last edited by NBVC; 06-22-2010 at 09:18 AM. Reason: Removed again confidential workbook
Please don't include real names in your attachments....
You need to also drag down the Zip Code to the Row Labels area.
In XL2007, to get the top 10.. in the actual Pivot table, click on the drop down arrow next to the Row Labels column header... and select Value Filters and then select Top 10... then change the 10 to 15.
See here: http://www.techonthenet.com/excel/pi...top10_2007.php
Last edited by NBVC; 06-22-2010 at 09:23 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks