Setting up rank within changing Pivot Table

[MyDogJake]

I'm trying to rank two columns (never change) but length of columns (rows) will change as I add or remove variables. I am referencing a pivot table.

The layout is always the same.

My ranking example formula for Atlanta is: =IFERROR(RANK($L6,$L$6:$L$84,0),"") ...where 84 is the last row of values...

The formula should skip rows that are the next market, in this case row 85
The new rank should begin on row 86 to XXXX. ... and the process repeats.

Thank you for any help.

PS: I tried to post a picture but nothing worked ... any idea on how to post a snippit of my workbook?

-- removed inline image ---

 

[Jerry Sullivan]

If your Pivot Table has subtotals, you could try something like this:

Excel Workbook
	A	B	C	D	E
1	City	Salesperson	Sum of Sales		Rank
2	Atlanta	Name 01	1,688		2
3		Name 02	977		3
4		Name 03	880		4
5		Name 04	2,132		1
6	Atlanta Total		5,677
7	Boston	Name 01	2,319		1
8		Name 02	1,538		3
9		Name 03	1,880		2
10	Boston Total		5,737
11	Chicago	Name 01	1,210		2
12		Name 02	2,421		1
13	Chicago Total		3,631
14	Grand Total		15,045
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


You might need to modify this if your PT layout is different (IE subtotals at Top).

PS: I tried to post a picture but nothing worked ... any idea on how to post a snippit of my workbook?

See the FAQs for instruction on how to download MrExcel's HTML Maker.

 

[MyDogJake]

This looks fantastic! ..way above my formula skills

The only difference between my layout and the one you created for example, is that I have two separate columns; one for 52wks and the other for 24wks. Contained within each column is $ sales. Each row represent items.
Each section is a market.
A_B___C______D__________E__________F
52wk/24wk ______________52wk_______24wk
_____Atlanta Census Trade
1 1------------ Item 1---------52000----------26150
2 3-------------Item 2---------48000----------23500
3 2-------------Item 3---------43500----------24500
_____Total Atlanta

Next market =Boston ... it might have 50 items, followed by Cincinatti with only 28 etc... by choosing the filter I could have many markets or as few as one.


Thank you so much for your help.

Go Ducks!!
Mike (from Portland!)

 

[Jerry Sullivan]

Hi Mike,

Here's how that would look for 2 columns of rankings to the left of the PivotTable.

Excel Workbook
	A	B	C	D	E	F
1					Values
2	52 wk	24 wk	City	Items	Sum of 52 wk	Sum of 24 wk
3	1	1	Atlanta	Item 1	52,000	26,150
4	2	3		Item 2	48,000	23,500
5	3	2		Item 3	43,500	24,500
6			Atlanta Total		143,500	74,150
7	3	2	Boston	Item 1	24,000	38,000
8	2	1		Item 2	135,000	63,000
9	1	3		Item 3	157,000	4,000
10			Boston Total		316,000	105,000
11			Grand Total		459,500	179,150
Sheet2

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Go Ducks!!
Mike (from Portland!)

Thanks. You're the first to mention my UO Duck's Avatar!