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

Name	Score	Rank
Anthony	10	1
Mary	8	3
Mike	9	2
Richard	7	4

<tbody>
</tbody>

Not very beautiful, but useful.

M.

 

[lancebaldwin]

Re: Need Help To Break RANK Tie

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

<tbody>
</tbody>