Organizing ranked data from one column into another column

[msj2487]

Hi everyone, rank amateur Excel lover looking for assistance with something that seemingly feels it should be much easier than I'm making it. I would appreciate any assistance!

I have data ranked in one column, but I'm wanting to display it in a separate column in order without using a basic sort function.

A------- B -- C
2019 -- 64 -- 1
2018 -- 55 -- 3
2017 -- 47 -- 5
2016 -- 56 -- 2
2015 -- 42 -- 7
2014 -- 45 -- 6
2013 -- 49 -- 4
2012 -- 40 -- 10
2011 -- 41 -- 8
2010 -- 41 -- 8

So Column C is a rank formula for Column B.. =RANK(B1,$B$1:$B$100)

What I'd like to do though is have a separate Column, say Column E pull data from Column C and put it in order, essentially sorting the ranks in order:

Column E
1
2
3
4
5
6
7
8
8

Then, I would ultimately want a Column D to display the corresponding values from Column A that matches the sorted and ranked values in Column E without skipping the duplicate data; I know I've done this before so that both the 8's have a different value (2010 & 2011) instead of just both reading 2011 since it would be found first in a lookup function, but I am completely over thinking this and probably approaching all of it horrendously, so can any assist with this madness?

Thanks so much!

 

[DRSteele]

I have no clue how to do this without the new dynamic functions SORTBY and SORT.

MrExcel posts18.xlsx
	A	B	C	D	E	F
1	year	value	rank		sorted rank	sorted year
2	2019	64	1		1	2019
3	2018	55	3		2	2016
4	2017	47	5		3	2018
5	2016	56	2		4	2013
6	2015	42	7		5	2017
7	2014	45	6		6	2014
8	2013	49	4		7	2015
9	2012	40	10		8	2011
10	2011	41	8		8	2010
11	2010	41	8		10	2012
Sheet15

Cell Formulas
Range	Formula
C2:C11	C2	=RANK.EQ(B2:B11,B2:B11)
E2:E11	E2	=SORT(C2#)
F2:F11	F2	=SORTBY(A2:A11,C2#)
Dynamic array formulas.

 

[msj2487]

Thank you, this is the exact result I'm looking for, however, I'm struggling with the SORT and SORTBY functions.

I'm getting a #ERROR! when I put in the formula for E2 listed above; do I need to be using a specific version of Excel for these dynamic functions to be recognized? Also, I'm terribly sorry for my ignorance here haha!

 

[DRSteele]

Well, you need to have Excel 365 in order for those functions to work. I have a feeling that trying to achieve this result with pre-365 functions will be a frustrating experience.

[There is no need to apologise!]