Need a formula to simulate & replace a pivot table

[psulion01]

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

 

[texasalynn]

not sure this is what you meant, but the ranking would give the order

Book6
	A	B	C	D	E	F	G	H	I
1	ID	Region	Production	Rank				Region Totals
2	111	North	25,000	1				North	30000
3	222	South	15,000	3				South	61000
4	333	East	3,000	11				East	18000
5	444	West	10,000	7				West	30000
6	555	West	14,000	5
7	666	South	22,000	2
8	777	South	3,000	12
9	666	North	5,000	9
10	777	South	8,500	8
11	777	South	12,500	6
12	333	East	15,000	4
13	555	West	2,500	13
14	555	West	3,500	10
Sheet1

 

[psulion01]

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

 

[texasalynn]

take a look at Chip Pearson's ranking
http://www.cpearson.com/excel/rank.htm

 

[psulion01]

Cool thanks... I think I may have it now.