Break RANK Tie

[lancebaldwin]

Hi,

I have two Ranked Columns

Example:

A B

1 3
2 4
3 1
4 1
5 5
6 1
7 2

I need to search COLUMN "B" For Highest Rank, and then once that is found, I need to have it tell me which of those have the highest rank in Column "A"....

So in this instance, it would be the 3rd one down, B=1 and A=3.... Can someone help me with what the formula would be to have it find the best of column A AFTER it finds the #1 in B?

8

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

Ok, I did that. Says it need to be approved by moderator.

 

[Joe4]

Re: Need Help To Break RANK Tie

Ok, I did that. Says it need to be approved by moderator.

It is visible now. Posts from new members that have code in them often get Moderated.

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

Ok, I did that. Says it need to be approved by moderator.

Ok. Now that we have a sample of your data (say in A1:U10) could you explain what are you trying to do? Expected results and where to place them (rows/columns).

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

OK, So....

A1:U10 Don't matter.... Not sure why it has those on your chart...

(TOP LEFT BOX IS BE51, TOP RIGHT BOX IS BY51)
(BOTTOM LEFT BOX IS BE64, BOTTOM RIGHT BOX IS BY64)

So the total box I sent you is BE51:BY64

So with those numbers....

BE60:BE64 is a RANK of BR60:BR64 (Lets Call this RANK 1)
BF60:BF64 is a RANK of BS60:BS64 (Lets Call this RANK 2)

I want the sheet to display the TOP 3 RANKED LINES FROM RANK 2 (Column BF). It needs to display that entire line of each of the top 3 ranked from RANK 2.

Those are displayed at the top from:
BG55:BY55 (#1 Rank)
BG56:BY56 (#2 Rank)
BG57:BY57 (#3 Rank)

So, you can see that all of the numbers in RANK 2 are the same (a tie) = 1605 is what you see on your example.
My sheet goes down 10,000 rows, so there is a TRUE #1 rank, but it is also duplicated 20-30x just as you see the #1 605 is duplicated.

So pretend that the 1605 is the RANK of "#1 ".... Once it finds that RANK of #1 , I need it to do a tiebreaker, and choose the best #1 in RANK 2, by using the RANK 1 column (BE60-BE64) as the tie breaker.

So in this event, lets continue to pretend that the RANK 2 isn't 1605... Say its #1 ..... So it would find all the #1 ranks tied from BF60:BF64, then it would break that tie by finding the lowest number in column BE, which is (BE61), which equals "1".
So that line would then be displayed from BG55:BY55. Then the line under it would find the next highest number in the RANK 1 column, and list that whole line on (BG56:BY56), that would be the #3 which is located in BE60, and then next it would need to find
the 3rd highest tie breaker in COLUMN BE, which would be the #1 3, and that entire line would be listed up top in BG57:BY57.

Hope that makes sense?

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

I think I understand what you need. To solve the problem I would create an auxiliary column to calculate the final rank (BD in the example below)

Something like:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

BD​

[/TD]
[TD="bgcolor: #DCE6F1"]

BE​

[/TD]
[TD="bgcolor: #DCE6F1"]

BF​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

59​

[/TD]
[TD]

FinalRank​

[/TD]
[TD]

Rank1​

[/TD]
[TD]

Rank2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

60​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

1605​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

61​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1605​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

62​

[/TD]
[TD]

5​

[/TD]
[TD]

25​

[/TD]
[TD]

1605​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

63​

[/TD]
[TD]

4​

[/TD]
[TD]

17​

[/TD]
[TD]

1605​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

64​

[/TD]
[TD]

3​

[/TD]
[TD]

13​

[/TD]
[TD]

1605​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in BD60 copied down
=COUNTIF(BF$60:BF$64,"<"&BF60)+COUNTIFS(BF$60:BF$64,BF60,BE$60:BE$64,"<"&BE60)+1

This done, you can use the FInalRank column (BD) to get the top three ranked using VLOOKUP.

Hope this helps

M.