Rank Only Groups with Same Characteristic

[wzxchange]

Good morning,

I've been trying to figure this out for a while, but haven't been able to find any resources. Thought you guys will be able to help me solve this.

I would like to create a spreadsheet that creates kind of a leaderboard based on geography. To illustrate:

Tab 1:

• Drop down menu with locations (e.g.: Texas Market, Arizona Market, California Market)
• Data flows into tab based on vlookup to menu (i.e.: Column B = revenues, Column C = cost of goods sold)
• More importantly, 5 Most Profitable Customers and 5 Least Profitable Customers

Tab 2:

• Data in the following order (Customer Name, Market, Revenues, COGS, Gross Margin, Rank) - approximately 10,000 rows

Currently, I'm using a rather manual custom sort option to get the customers to line up in order of market, before using the RANK function in excel to sort by gross margin. However, I'm wondering if there's an "IF" statement of some sort where I can create a "RANK customer based on Gross Margin among peers with same Market".

Any help will be greatly appreciated. Thank you very much!

 

[vaskov17]

Have you tried a pivot table and filtering top and bottom values?

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