Sorting two rows as a group

[Johnmus]

Hi All,

I have created a band competition recap which list schools and their scores. The groups are entered based on performance time, then ranked within their class. I want to sort the groups by "place" located in column AX. The problem is that I have another row underneath the row with scores that includes rankings (ordinals) for each sub caption listed in the row above. So I need to keep the two rows together for sorting purposes. See truncated sample below.

Row # School Name ....Scores for sub captions ..........................................................Total ......Place (Col AX)
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]5[/TD]
[TD]School 1[/TD]
[TD]98[/TD]
[TD]88[/TD]
[TD]77[/TD]
[TD]55[/TD]
[TD]66[/TD]
[TD]77[/TD]
[TD]44[/TD]
[TD]88[/TD]
[TD]67[/TD]
[TD]88[/TD]
[TD]67.8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ordinals[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 3[/TD]
[TD] 6[/TD]
[TD] 5[/TD]
[TD] 2[/TD]
[TD] 4[/TD]
[TD] 1[/TD]
[TD] 3[/TD]
[TD] 1[/TD]
[TD] 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]School 2[/TD]
[TD]99[/TD]
[TD]92[/TD]
[TD]81[/TD]
[TD]75[/TD]
[TD]71[/TD]
[TD]88[/TD]
[TD]69[/TD]
[TD]90[/TD]
[TD]81[/TD]
[TD]89[/TD]
[TD]83.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Ordinals[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 3[/TD]
[TD] 3[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is that even possible?

Thanks for any suggestions!

 

[Fluff]

You could use a helper column.
in AY2 put =IF(AX2<>"",AX2,AY1+0.5) & fill down
Then you can sort on column AY

HTH

 

[Johnmus]

That works perfectly! Thanks for the response, it is appreciated.

John

 

[Fluff]

Glad to help & thanks for the feedback

 

[Johnmus]

Noticed that when there is a tie in a class the formula does not work. Anyway around that?

 

[Fluff]

Try
=IF(AX2<>"",AX2,AY1+0.05)+(COUNTIF(AX$2:AX2,AX2)/10)

 

[Johnmus]

Try
=IF(AX2<>"",AX2,AY1+0.05)+(COUNTIF(AX$2:AX2,AX2)/10)

Works great! Thank you!
John

 

[Fluff]

Glad to help & thanks for the feedback