Sorting two data sets on Cluster chart

I'd plot 4 series:
- two with data from all other persons (sorted in each category) with empty value where "the person" shall show-up.
- and two with data just for this person but located in an appropriate position
so having name in C2 (I meade it selectable with data validation as a list) and collected data in A5:C13 only 2 formulas. In E5 and copy 1 right and 9 down:

Formula:

=IF(SMALL(B$5:B$13,ROW($A1))=INDEX(B$5:B$13,MATCH($C$2,$A$5:$A$13,0),0),"",SMALL(B$5:B$13,ROW($A1)))

and in G5 (also copied right and down):

Formula:

=IF(E5="",VLOOKUP($C$2,$A$5:$C$13,COLUMN(B$4),FALSE),"")

plotting (see attached file):
stage1: all 4 series plotted
stage2: two series formatted to be plotted on secondary axis
stage3: deleting secondary axis, removing legend
Final: just final touch - changing formatting to gray for both primary series, red for secondary.

Of course if needed data labels can be added. To avoid shoving zeros custom formatting can be used. for instance: "0,0," (second comma is important here)

Glad to hear that and thanks for makting thread solved and for reputation.

Note, that in this sample, there is no ex-aequo results. If in reality you can face such situations look for more advanced ranking methods than just SMALL - for instance http://www.excelforum.com/excel-gene...cal-order.html etc.

Hi Mike,

Please take some time and read our http://www.excelforum.com/forum-rule...rum-rules.html
This time focus on rule 2. But read them all, and few tips below the list too. It will pay...