Function to Sort data based on value to the right

[hajiali]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


Using the above table column A is an array Function that pulls data from another sheet which will Every time I sort Column B to Sort A to Z it always goes back to the normal Sort. I want column C to pull the data form Column A and List them based on the order of the number to the right so. So Results for Column C Should be as Follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]D[/TD]
[/TR]
[TR]
[TD]H[/TD]
[/TR]
[TR]
[TD]B[/TD]
[/TR]
[TR]
[TD]I[/TD]
[/TR]
[TR]
[TD]J[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]E[/TD]
[/TR]
[TR]
[TD]F[/TD]
[/TR]
[TR]
[TD]G[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

As long as your data won't have duplicates, as in your example, then this will work:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]D[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]H[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]B[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]I[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]J[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]A[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]E[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]F[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]G[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]C[/TD]

</tbody>

hajiali​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1
[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,MATCH(AGGREGATE(15,6,$B$1:$B$10,ROW()),$B$1:$B$10,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hajiali]

Thank you Toadstool. works Great