Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rnak, median, etc. within uneven sized groups (Excel 2003)

    Previously (see "Macro for conditional formatting, ranking, etc. (Excel 2003)") I received some outstanding help with ranking data within a large spreadsheets where the groups were of same size throughout. So for example, there might be 10,000 records, but there'd be 10 each for 1000 zip codes. We'd then go through these 10 at a time and compile stats on rank, median, etc. This worked great!

    Now, I've got a similar situation, where I want to do the same thing within each zip code, but with a significant difference: there are varying numbers of records for each zip code. So let's say I've got a column named "Zip code" and another named "Price". I'd like to be able to get the rank of each price by zip code, as well as the median, minimum, etc. as before. Is there some way to do this?

    Any suggestions would be greatly appreciated!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rnak, median, etc. within uneven sized groups (Excel 2003)

    Can you post a small sample workbook (the data can be fake)? Thanks.

  3. #3
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rnak, median, etc. within uneven sized groups (Excel 2003)

    Thanks

    The data I am sending has three columns: Company, Zip Code, and Price. What I'd like to be able to do is several things:

    (1) Filter out duplicates. This seems easy enough with "Unique records only" in Advanced Filter, correct?

    (2) Then, I'd like to reduce the list to include only the lowest price for each company within each zip code.

    Once I've got that, I should have a worksheet with exactly nine records per zip code, one per company, and from there I can readily adapt the formulae you kindly sent me a few weeks ago for rank, median, etc. for groups of 9 rather than 10 records.

    Many thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rnak, median, etc. within uneven sized groups (Excel 2003)

    I'll look at it later today.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rnak, median, etc. within uneven sized groups (Excel 2003)

    You can use a pivot table to create a list of the lowest price for each company within a zip code - see the attached version.

  6. #6
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rnak, median, etc. within uneven sized groups (Excel 2003)

    Many thanks! I came up with another solution using multiple sorts where I was able to get rid of the higher prices. It took quite a few steps, but did eventually work. I'll look at yours now -- much appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •