Pivotting - T. Valko
 

I posted the below followup question a few days after the original question
had been answered by T. Valko, so I don't think it has been seen. I am
deparate for a resolution so at some risk I am posting my followup question
'on its own.' [Original post - "Pivotting w/Varying Column"]

The formula below does not break ties, I end up with 1,2,3,4,4,6,7,8,8
This presents a problem when I attempt to pull the names in the ranked order
from the pivot table I get the first tied name twice.

Any chance you could show me how to add the tiebreaker to the formula? I
thought it was there but apparently not. Oh, BTW I needed to do some error
trapping and have been using the formula as it appears below.

=IF(ISNA(RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH(" Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)))-1),"",RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Gra nd
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)))-1)

Pivotting - T. Valko
 

See my most recent reply at the original thread.

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
I posted the below followup question a few days after the original question
had been answered by T. Valko, so I don't think it has been seen. I am
deparate for a resolution so at some risk I am posting my followup
question
'on its own.' [Original post - "Pivotting w/Varying Column"]

The formula below does not break ties, I end up with 1,2,3,4,4,6,7,8,8
This presents a problem when I attempt to pull the names in the ranked
order
from the pivot table I get the first tied name twice.

Any chance you could show me how to add the tiebreaker to the formula? I
thought it was there but apparently not. Oh, BTW I needed to do some
error
trapping and have been using the formula as it appears below.

=IF(ISNA(RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH(" Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)))-1),"",RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Gra nd
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand
Total",B$5:L$5,0)))-1)