Generating a list ranked by value

[Patrick020]

Hi everyone

I have a table in A1:D9 in which a unit is plotted for each person against various locations.

Cell G1 is a data validation/drop down in which you can select a person from the table (in the example below I have taken Person 2). The formula therefore needs to reference cell G1.

I would like a formula to drag down from G2 which lists the locations for that person from the highest ranked to the lowest ranked. In the example below New York has a 12 for Person 2 which is the highest value – hence NewYork is listed first.

If possible I would also like a formula to drag down from F2which pulls the unit associated with that person and location – so in the example below F2 is 12 as Person 2 plots 12 for New York.

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"] A<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] B<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] C<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] D<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] E<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] F<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] G<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] City<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] Person 1 <o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 2<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 3<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] Unit <o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Person 2<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Berlin <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] New York<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Los Angeles<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Paris <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] London <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Los Angeles <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Madrid <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Madrid<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Moscow <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] New York <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Paris <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 9<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Rome<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 11<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 10<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully that is fairly clear but let me know if not!

Thanks
Patrick

 

[Marcelo Branco]

Try

Formula in F2 copied down
=IFERROR(LARGE(INDEX(B$2:D$100,0,MATCH(G$1,B$1:D$1,0)),ROWS(F$2:F2)),"")

Array formula in G2 copied down
=IF(F2="","",INDEX(A$2:A$100,SMALL(IF(INDEX(B$2:D$100,0,MATCH(G$1,B$1:D$1,0))=F2,ROW(A$2:A$100)-ROW(A$2)+1),COUNTIF(F$2:F2,F2))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[AlanY]

similar approach

Excel 2013/2016
	A	B	C	D	E	F	G
1	City	Person 1	Person 2	Person 3		Unit	Person 2
2	Berlin			6		12	New York
3	Los Angeles	3	3			7	Paris
4	London	8				3	Los Angeles
5	Madrid		1			1	Madrid
6	Moscow			2
7	New York	5	12
8	Paris		7
9	Rome			11
Sheet1

Cell Formulas
Range	Formula
F2	=LARGE(INDEX(A:D,,MATCH(G$1,$A$1:$D$1,0)),ROW(A2)-ROW($A$1))
G2	=INDEX(A:A,MATCH(F2,INDEX(A:C,,MATCH(G$1,$A$1:$D$1,0)),0))

 

[Marcelo Branco]

Hi Alan

Thoughts:
1. I think there a typo in your formula in G2
=INDEX(A:A,MATCH(F2,INDEX(A:C,,MATCH(G$1,$A$1:$D$1,0)),0))
Shouldn't it be?
=INDEX(A:A,MATCH(F2,INDEX(A:D,,MATCH(G$1,$A$1:$D$1,0)),0))

2. To deal with draws is necessary an array formula in G2
For example: change the value in C8 to 12 (instead of 7)

M.

 

[AlanY]

Hi Alan

Thoughts:
1. I think there a typo in your formula in G2
=INDEX(A:A,MATCH(F2,INDEX(A:C,,MATCH(G$1,$A$1:$D$1,0)),0))
Shouldn't it be?
=INDEX(A:A,MATCH(F2,INDEX(A:D,,MATCH(G$1,$A$1:$D$1,0)),0))

2. To deal with draws is necessary an array formula in G2
For example: change the value in C8 to 12 (instead of 7)

M.

yes, you're right.
thanks

 

[Patrick020]

Thank you both for responding so quickly.

=IF(F2="","",INDEX(A$2:A$100,SMALL(IF(INDEX(B$2:D$100,0,MATCH(G$1,B$1:D$1,0))=F2,ROW(A$2:A$100)-ROW(A$2)+1),COUNTIF(F$2:F2,F2))))

This works absolutely perfectly - Thanks!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.