rankif ?? (2 column criteria)

Posted by Rey on August 19, 2001 3:02 AM

Hi folks, I've been sitting here and thinking about this for hours but I'm stumped. Any input would be much appreciated. I captured a small part of the screen to show you, I get lost trying to explain it :)

Problem Snapshot

I'm trying to rank col LVR based on col TR. If TR =1 and TD =09-Jul-01 then I manually enter formula:

=RANK(K2,$K$2:$K$7)

I'd like to copy the formula down the col

Col TR's value changes, the number 1 can repeat (or any other number) somewhere down the col that's why I had to involve the TD to try and isolate it.

I hope that made sense... thank you.

Posted by Aladin Akyurek on August 19, 2001 6:41 AM

Are you just interested in ranking just a single block of LVRs (with correspnding values of TR=1)? What about LVRs that corespond to TR=2 etc?

Posted by Rey on August 19, 2001 11:06 AM

I'd like to rank all blocks of LVRs based on the blocks of TRs, like with our example a block of 1, next a block of 2 and so on...

Thanks

Posted by Aladin Akyurek on August 20, 2001 3:18 AM

Rey,

What folows requires that the data are sorted by column C in ascending order. Ranking starts anew for each block & ties get the same rank.

In T1 enter: Col To Rank [ just a label ]
In T2 enter: =COLUMN(K:K) [ or just: 11 ]
In T3 enter: Lookup Table [ just a label ]
In T4 enter: =IF(LEN(U2)>0,ADDRESS(ROW(U2),COLUMN(U2))&":"&ADDRESS(COUNT(W:W)+1,COLUMN(W:W)),"")

In U1 enter: Blocks [ just a label ]
In U2 enter: =IF(ROW()-1&LT;=MAX(C:C),ROW()-1,"") [ copy down as far as needed ]

In V1 enter: EndRows [ just a label ]
In V2 enter: =IF(LEN(U2)>0,COUNTIF(C:C,U2)+1,"")
In V3 enter: =IF(LEN(U3)>0,COUNTIF(C:C,U3)+V2,"") [ copy down as far as needed ]

In W1 enter: StartRows [ just a label ]
In W2 enter: =IF(U2,ROW(2:2),"")
In W3 enter: =IF(LEN(V3)>0,V2+1,"") [ copy down as far as needed ]

Note. The above scheme of computations can also be done in a different worksheet.

Finally,

in Q2 enter: =IF(LEN(K2)>0,RANK(K2,INDIRECT(ADDRESS(VLOOKUP(C2,INDIRECT($T$4),3),$T$2)&":"&ADDRESS(VLOOKUP(C2,INDIRECT($T$4),2),$T$2))),"")

Aladin

Posted by Rey on August 20, 2001 9:35 AM

Thanks Aladin,
I'll try this when my brain is functioning at its best and I'll let you know the result. Thank you for the help! Really appreciated.

Rey