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!