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

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

Try this array formula

=MIN(IF(B1:B7=1,A1:A7))
Ctrl+Shift+Enter

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

This is my formula now....

[FONT=&quot]=IF(COUNTIF($BF$60:$BY$9778,1)=1,VLOOKUP(1,$BF$60:$BY$9778,3,0),VLOOKUP(1,$BE$60:$BY$9778,12,0))

Where would I enter the "MIN"?

[/FONT]

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

This is my formula now....

=IF(COUNTIF($BF$60:$BY$9778,1)=1,VLOOKUP(1,$BF$60:$BY$9778,3,0),VLOOKUP(1,$BE$60:$BY$9778,12,0))

Where would I enter the "MIN"?

Forgot to say...
Welcome to Mr Excel forum

You've asked how to find the min value in column A where B =1. This is what the formula in post 2 does.
I don't know what you are trying to do with the formula you posted above. Try to explain. A small data sample along with expected result(s) would be helpful.

To post a data sample see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

https://drive.google.com/open?id=0B408qCycsKyJX2hsSXdpT0k4NDQ

https://drive.google.com/open?id=0B408qCycsKyJX2hsSXdpT0k4NDQ

IMAGE

Thanks Marcelo!

I just attached link to screen shot. Let me know if you understand it?

So, in a nutshell, I have a list of 10,000 entries.... I have ranked in COLUMN "BE" and "BF" by two different statitics.

I have it displaying the 3 TOP RANKED entries (Full Row Across) by TOP RANKED COLUMN "BE" (BG51:BY57)

Then.... Under that.....

I have it displaying the 3 TOP RANKED entries (Full Row Across) by TOP RANKED COLUMN "BF" (BG55:BY57)

The BE column is okay the way it is, because there are no duplicates. BUT the BF column has alot of duplicates.

So I am trying to have it find the top ranked entry in column BF, which is "1", but there are 30 of them... So NOW, I need it to look at those 30 entries, and tell me which one is the MIN value (highest rank of those, but its not necessarily the digit of 1, so I cant ask it to find 1, it needs to find the lowest of what is there.

I hope I make sense....

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

I appreciate your effort to provide a data sample, but unfortunately images are not helpful - it's not possible to copy/paste the data for testing purposes.
As i said take a look at the link above (post 4) and try to post a data sample here (in a forum page): ~10 rows are enough. Otherwise, is hard to help.

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

Thanks Marcelo! I just tried and I cant figure out how to use the Excel Data Sample.... Im Sorry. Thankyou for trying to help! I appreciate it!

 

[Marcelo Branco]

Re: Need Help To Break RANK Tie

Try something very simple like this

In Excel i selected the range A1:C5
1.copy (Ctrl+C)
2.in this page
paste (Ctl+V)

type in the columns/rows of the data
Click the button Go Advanced
Click inside the table; click the icon Table Properties (last row of icons,second from left to right)
Pick Full Grid

Columns A to C; rows 1 to 5
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Score[/TD]
[TD="width: 64"]Rank[/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Not very beautiful, but useful.

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

[TABLE="class: grid, width: 713"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]LW[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]-1.4[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]4.0[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]LW[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]-1.4[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]4.0[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]LW[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]-1.4[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]4.0[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L3[/TD]
[TD]RZ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]POS[/TD]
[TD]DK$[/TD]
[TD]NM[/TD]
[TD]REST[/TD]
[TD]LINE[/TD]
[TD]PPLN[/TD]
[TD]TM[/TD]
[TD]OPP[/TD]
[TD]PASS[/TD]
[TD]RSH[/TD]
[TD]DvP[/TD]
[TD](=)[/TD]
[TD]GMPT[/TD]
[TD]P/PT[/TD]
[TD]P/PT[/TD]
[TD]PPOP[/TD]
[TD][/TD]
[TD]FPTS[/TD]
[TD]OPPs[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1605[/TD]
[TD]LW[/TD]
[TD="align: right"]$8,200[/TD]
[TD]Alex Ovechkin[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]-2.4[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]5.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1605[/TD]
[TD]LW[/TD]
[TD="align: right"]$8,200[/TD]
[TD]Alex Ovechkin[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]-1.4[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]-1.0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]1605[/TD]
[TD]LW[/TD]
[TD="align: right"]$8,200[/TD]
[TD]Alex Ovechkin[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]5.1[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]-2.0[/TD]
[TD="align: right"]11.9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]1605[/TD]
[TD]LW[/TD]
[TD="align: right"]$8,200[/TD]
[TD]Alex Ovechkin[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]-3.0[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1605[/TD]
[TD]LW[/TD]
[TD="align: right"]$8,200[/TD]
[TD]Alex Ovechkin[/TD]
[TD="align: right"]2[/TD]
[TD]L1[/TD]
[TD]P1[/TD]
[TD]WAS[/TD]
[TD]TOR[/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]-4.0[/TD]
[TD="align: right"]8.7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
</tbody>[/TABLE]