rank by criteria

shannonsmith

New Member
Joined
Apr 12, 2012
Messages
37
I essentially need a formula that says for every market (there are 20), rank all of the products within that market by $ Vol (highest to lowest).

I've tried to filter on the markets and then rank the product, but the file becomes very slow because there are 20 Markets, each containing roughly 30,000 products. Any suggestions would be greatly appreciated.

MKT
PROD
$ Vol
Total Retail
SUTTER HOME 1.5 LITERS
$ 96,201,869.00
Total Retail
SUTTER HOME 187 MILLITERS
$ 57,751,044.00
Total Retail
SUTTER HOME 750 MILLITERS
$ 58,107,054.00
Total Retail
SUTTER HOME 3 LITERS
$ -
Total Retail
SUTTER HOME
$ 212,059,967.00
Total Retail
BAREFOOT 1.5 LITERS
$ 153,541,287.00
Total Retail
BAREFOOT 187 MILLITERS
$ 17,358,324.00
Total Retail
BAREFOOT 750 MILLITERS
$ 188,904,750.00
Total Retail
BAREFOOT
$ 359,804,361.00
Total Retail
BRAND FAMILY
$ 571,864,328.00
TARGET TOTAL CTA
SUTTER HOME 1.5 LITERS
$ 2,177,572.00
TARGET TOTAL CTA
SUTTER HOME 187 MILLITERS
$ 2,388,573.00
TARGET TOTAL CTA
SUTTER HOME 750 MILLITERS
$ 1,296,227.00
TARGET TOTAL CTA
SUTTER HOME 3 LITERS
$ -
TARGET TOTAL CTA
SUTTER HOME
$ 5,862,372.00
TARGET TOTAL CTA
BAREFOOT 1.5 LITERS
$ 4,749,233.00
TARGET TOTAL CTA
BAREFOOT 187 MILLITERS
$ 28.00
TARGET TOTAL CTA
BAREFOOT 750 MILLITERS
$ 5,933,944.00
TARGET TOTAL CTA
BAREFOOT
$ 10,683,205.00

<tbody>
</tbody>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Formula in D2 is copied down:
Excel Workbook
ABCD
1MKTPROD$ Vol
2Total RetailSUTTER HOME 1.5 LITERS96,201,869.005
3Total RetailSUTTER HOME 187 MILLITERS57,751,044.007
4Total RetailSUTTER HOME 750 MILLITERS58,107,054.006
5Total RetailSUTTER HOME 3 LITERS10
6Total RetailSUTTER HOME1,296,227.009
7Total RetailBAREFOOT 1.5 LITERS153,541,287.004
8Total RetailBAREFOOT 187 MILLITERS17,358,324.008
9Total RetailBAREFOOT 750 MILLITERS188,904,750.003
10Total RetailBAREFOOT359,804,361.002
11Total RetailBRAND FAMILY571,864,328.001
12TARGET TOTAL CTASUTTER HOME 1.5 LITERS2,177,572.006
13TARGET TOTAL CTASUTTER HOME 187 MILLITERS2,388,573.005
14TARGET TOTAL CTASUTTER HOME 750 MILLITERS1,296,227.007
15TARGET TOTAL CTASUTTER HOME 3 LITERS9
16TARGET TOTAL CTASUTTER HOME5,862,372.003
17TARGET TOTAL CTABAREFOOT 1.5 LITERS4,749,233.004
18TARGET TOTAL CTABAREFOOT 187 MILLITERS288
19TARGET TOTAL CTABAREFOOT 750 MILLITERS5,933,944.002
20TARGET TOTAL CTABAREFOOT10,683,205.001
Sheet
 
Upvote 0
p45cal- I can't explain my gratitude for helping me out. This formula will save me hours of work. Thank you so much. If you have time to respond, could you explain the formula? For the life of me I can't figure out how the second half of the formula works, which I assume is the rank part. If you don't have time, no big deal. I'm just glad it works!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top