locked
filtering zip codes by frequency RRS feed

  • Question

  • we have a column of client zipcodes that we would like to filter by frequency. currently it appears it filters them by numeric value, which is no help! is there a way to customize the way the information filters so that it does it by most occurences?

    thanks!

    Wednesday, July 25, 2012 6:46 PM

Answers

All replies

  • Hello:

    Hello TCHD:

    To confirm your requirements, you would like the zip codes to be filtered by most occurrences.  Did you want the number of occurrences to be displayed in the column to the right?

    For example,

    Zip code 91701 has 2 occurrences, 99940 has 20, 98823 has 10 and 99345 has 5:

    The filtered column(s) consisting of 4 rows would look like this:

    99940 (20)
    98823 (10)
    99345 (5)
    91701 (2)

    Correct?


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, July 25, 2012 7:09 PM
  • Insert a column of formulas, using

    =COUNTIF(A:A,A2)

    in row 2 to count the frequency of zipcodes in column A.  Then copy down.

    You can then sort by frequency and then by zipcode, to group them in order of occurences.


    HTH, Bernie

    Wednesday, July 25, 2012 7:42 PM
  • Hi there,

    Originally we had not planned on having the number of occurences to be displayed in the column to the right, however, if that is the only or even the best way to make that happen then we can adjust.

    On a daily basis we don't necessarily need to know how many of each zip code, nor the top ten zip codes we are serving. This is more for generating reports which is done monthly or quarterly.

    As I mentioned before when we try to sort it arranges the zips by numerical order instead of frequency, which does us no good!

    Thanks!

    Thursday, July 26, 2012 2:17 PM
  • If you do a pivot table, you can create a top 10 (or any number, actually) report very easily.

    http://www.contextures.com/excel-pivot-table-filters-top10.html


    HTH, Bernie

    • Proposed as answer by evohnave Thursday, July 26, 2012 4:48 PM
    • Marked as answer by Quist Zhang Tuesday, July 31, 2012 7:47 AM
    Thursday, July 26, 2012 2:37 PM
  • Hi,

    Thanks for responding. This is very helpful for sorting the information, however, if I am reading it right it is still sorting the zip codes by highest numerical order, instead of frequency. It looks like you can build custom filters, is there a way to change it to frequency instead of amount?

    Thursday, July 26, 2012 10:50 PM
  • With a pivot table, you can sort your data based on any field, in any order.

    HTH, Bernie

    Friday, July 27, 2012 12:44 PM
  • With a pivot table, you can sort your data based on any field, in any order.

    HTH, Bernie

    Friday, July 27, 2012 12:44 PM