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

    Macro for conditional formatting, ranking, etc. (Excel 2003)

    I am working with large spreadsheets where there might be 10,000+ rows. There will be three main columns: Company, Zip Code, and Price. The same ten companies will be repeated over and over in same order for 1000+ zip codes, with a price for each company in each zip code. I'd like to be able to set up some sort of macro or process to do the following (over and over again, for the ten rows in each of the 1000+ zip codes):

    (1) Add a column that ranks the companies 1 to 10 (ascending) by their Price.
    If our company is 1, 2, or 3, turn that row green.
    If our company is 8,9,10, turn that row red.

    (2) Add a column that shows Price difference from the MIN for that Zip Code.
    Add a column that shows Price difference from the MAX for that Zip Code.
    Add a column that shows Price difference from the MEDIAN for that Zip Code.
    Add a column that shows Price difference from the AVERAGE for that Zip Code.

    (3) Add a column that shows Price % difference from the MIN for that Zip Code.
    Add a column that shows Price % difference from the MAX for that Zip Code.
    Add a column that shows Price % difference from the MEDIAN for that Zip Code.
    Add a column that shows Price % difference from the AVERAGE for that Zip Code.

    These are all things I can do easily enough for one group of tne rows, but my efforts to extend these processes to a second group have not succeeded, much less to 1000+ groups.

    If I can figure out a way to do the above, my next step would be that I'd like to go through the worksheet by company and show each companies average for each of the above columns.

    Companies are currently listed by name, but it would certainly be possible to assign number values to them in an extra column if that would make this easier.

    Any suggestions from anyone out there would be greatly appreciated!

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

    Re: Macro for conditional formatting, ranking, etc. (Excel 2003)

    Welcome to Woody's Lounge!

    I think this can be done with formulas - see the attached example. All formulas were created in row 2, then filled down as far as the data go.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for conditional formatting, ranking, etc. (Excel 2003)

    Appreciate the solution Hans.

    I wonder, though, what happens with the MIN when one of the top 10 has a zero value. OP didn't indicate one way or the other, but I know that on some projects I have worked on, there was a possibility that 0 occurred in the ranking, in which case the the MIN would have to be altered to be "MIN non-zero solution". Just a thought.

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

    Re: Macro for conditional formatting, ranking, etc. (Excel 2003)

    You'd have to use an array formula (confirmed with Ctrl+Shift+Enter) looking like this:

    =MIN(IF(range>0,range))

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

    Re: Macro for conditional formatting, ranking, etc. (Excel 2003)

    Many thanks, I will give these a try and see. Much appreciated!

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

    Re: Macro for conditional formatting, ranking, etc. (Excel 2003)

    This worked perfectly -- many thanks!

Posting Permissions

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