Sorting fishing data in 2 sectors

Best,
In our fishing club we want to use a different way of sorting competition data from this year onwards. Normally we just sort by weight from highest to lowest. Now we work with 2 sectors and we want to sort according to a different principle. 1st sector A, 1st sector B, 2nd sector A, 2nd sector B in case if sector A has the heighest weight. Otherwise it's first sector B, first sector A, seconde sector B, second sector A, ...

Now we just used the sort function from largest to smallest, but in this case this is a little more complicated to sort automatically. I have some basic knowledge of Excel, but I am unable to do this. Is there anyone who can help me with this? Thank you very much!

Check example in attachement.

Welcome to the forum.

Can I just check that this is definitely for Excel 2019 and not for 2021 or 365?

Not sure about your expected results - are they really in the right order? If Mario is fourth and Tom is third in sector A, why are they paired with third and fourth in sector B respectively?

OK - that's a huge shame. 2021 and 365 have the SORT function that would have made this much easier.

So it's now out of my wheelhouse, I am afraid, Someone else will assist.

columnH:

Formula:

=INDEX($A:$A,MOD(LARGE(IF($B$2:$B$11=$I2,$D$2:$D$11/1%%+ROW($2:$11)),COUNTIF($I$2:$I2,$I2)),100))

ColumnI

Formula:

=MID(IF(INDEX(B:B,MATCH(MAX(D:D),D:D,))="B",REPT("BA",MIN(COUNTIF(B:B,"A"),COUNTIF(B:B,"B")))&IFERROR(REPT("B",COUNTIF(B:B,"B")-COUNTIF(B:B,"A")),"")&IFERROR(REPT("A",COUNTIF(B:B,"A")-COUNTIF(B:B,"B")),""),REPT("AB",MIN(COUNTIF(B:B,"A"),COUNTIF(B:B,"B")))&IFERROR(REPT("B",COUNTIF(B:B,"B")-COUNTIF(B:B,"A")),"")&IFERROR(REPT("A",COUNTIF(B:B,"A")-COUNTIF(B:B,"B")),"")),ROW(A1),1)

ColumnJ

Formula:

=INDEX($C:$C,MOD(LARGE(IF($B$2:$B$11=$I2,$D$2:$D$11/1%%+ROW($2:$11)),COUNTIF($I$2:$I2,$I2)),100))

ColumnK

Formula:

=LARGE(IF($B$2:$B$11=$I2,$D$2:$D$11),COUNTIF($I$2:$I2,$I2))