Need a formula to simulate & replace a pivot table

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
I've got a quarterly report that i'm constructing, and each quarter i want the ability to just plug in my data sheet and have the report page update automatically.

One section of the report calls for a listing of the top 5 producers in a given region. The data would exist as follows (sorry...the HTML Maker site was down, so I need to post manually):

A B C
------------------------------------------------
1 | ID Region Production
2 | 111 North 25,000
3 | 222 South 15,000
4 | 333 East 3,000
5 | 444 West 10,000
6 | 555 West 14,000
7 | 666 South 22,000
8 | 777 South 3,000
9 | 666 North 5,000
10| 777 South 8,500
11| 777 South 12,500
12| 333 East 15,000
13| 555 West 2,500
14| 555 West 3,500


I want some way, other than using a pivot table, to return the subtotals of the top 5 producer IDs in each region. For example:

Region: South
Rank ID Total Prod
#1 666 27,000
#2 777 24,000
#3 222 15,000
...

I know how to get the desired output using pivot tables, but again, I want the sheet to allow me to plug in the data (on a separate sheet), and then have the top 5 or top X calculated and displayed on a section of a report.

Thoughts?

I'll revise the post once I can get the HTML maker (if you folks need to see more data or a cleaner layout to help me out).

Thanks,
Mike
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
not sure this is what you meant, but the ranking would give the order
Book6
ABCDEFGHI
1IDRegionProductionRankRegion Totals
2111North25,0001North30000
3222South15,0003South61000
4333East3,00011East18000
5444West10,0007West30000
6555West14,0005
7666South22,0002
8777South3,00012
9666North5,0009
10777South8,5008
11777South12,5006
12333East15,0004
13555West2,50013
14555West3,50010
Sheet1
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Yep, the ranking looks like a step in the right direction, but I need to rank by ID, not by production value. Basically need to subtotal the IDs and then rank them within each region, so there would be four #1 rankings (North, South, East, West). For example, the #1 ranked ID in the South would be ID# 666.

Again, want to try to do this without pivot tables, as the data will change each quarter and I want to be able to plug in the data sheet without needing to do any adjustments to the formula/sheet.

Thanks
Mike
 

Forum statistics

Threads
1,181,658
Messages
5,931,272
Members
436,786
Latest member
Deniel

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
Top