Sorting Rank Results

[JTL9161]

I have a table that I determine the rank # with this formula:=SUMPRODUCT((ABS(AF7-0)>ABS(AF$5:AF$19-0))+0)+1

To get this:

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1938;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:512;width:11pt" width="14"> </colgroup><tbody>
[TD="class: xl65, width: 64"]Rank #[/TD]
[TD="width: 53"][/TD]
[TD="width: 14"][/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]JKL[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl65"]GGHHI[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]XYZ[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]AABCC[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]DDEEFF[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]GHI[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]MNO[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ABC[/TD]
[TD="class: xl65"]8[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]DEF[/TD]
[TD="class: xl65"]8[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]STU[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6
[/TD]
[TD="class: xl65"]VW[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6
[/TD]
[TD="class: xl65"]PQR[/TD]
[TD="class: xl65"]6
[/TD]

</tbody>

What I looking to do is on another spreadsheet in the workbook put these in order by rank.

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1938;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:512;width:11pt" width="14"> </colgroup><tbody>
[TD="class: xl65, width: 64"]Rank #[/TD]
[TD="width: 53"][/TD]
[TD="width: 14"][/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ABC[/TD]
[TD="class: xl65"]8[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]DEF[/TD]
[TD="class: xl65"]8[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]JKL[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]GHI[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]MNO[/TD]
[TD="class: xl65"]7[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]XYZ[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]AABCC[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]DDEEFF[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]STU[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]VW[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]PQR[/TD]
[TD="class: xl65"]6[/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl65"]GGHHI[/TD]
[TD="class: xl65"]5[/TD]

</tbody>

Is there a formula that will take the 1st table and put it into rank order like the 2nd table on another spreadsheet?

Thank for you help
James

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1
2	3	JKL	7			1	ABC	8
3	12	GGHHI	5			1	DEF	8
4	6	XYZ	6			3	JKL	7
5	6	AABCC	6			3	GHI	7
6	6	DDEEFF	6			3	MNO	7
7	3	GHI	7			6	XYZ	6
8	3	MNO	7			6	AABCC	6
9	1	ABC	8			6	DDEEFF	6
10	1	DEF	8			6	STU	6
11	6	STU	6			6	VW	6
12	6	VW	6			6	PQR	6
13	6	PQR	6			12	GGHHI	5
ADP

Cell Formulas
Range	Formula
F2	=SMALL($A$2:$A$13,ROWS($A$1:A1))
G2	=IFERROR(INDEX(B$2:B$13,AGGREGATE(15,6,(ROW(B$2:B$13)-ROW(B$2)+1)/($A$2:$A$13=$F2),COUNTIF($F$2:$F2,$F2))),"")
H2	=IFERROR(INDEX(C$2:C$13,AGGREGATE(15,6,(ROW(C$2:C$13)-ROW(C$2)+1)/($A$2:$A$13=$F2),COUNTIF($F$2:$F2,$F2))),"")

 

[JTL9161]

WOW! Aggregate?! I will give it a try. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[JTL9161]

Perfect! As usual. Thanks.