Ordering data by highest number and gender per same number

[Titanclaymore]

I am trying to create a formula that allows a set of data to be interrogated and then extract and display in set pattern. The data set will cover thousands of rows, for simplicity I have cut this to 15 with three columns

[TABLE="width: 204"]
<colgroup><col width="64" style="width:48pt"> <col width="76" style="width:57pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 76"]Hourly Rate[/TD]
[TD="width: 64"]Gender[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person D[/TD]
[TD="align: right"]8[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person E[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person F[/TD]
[TD="align: right"]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person G[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person H[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person I[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person J[/TD]
[TD="align: right"]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person K[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person L[/TD]
[TD="align: right"]6[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person M[/TD]
[TD="align: right"]6[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person N[/TD]
[TD="align: right"]6[/TD]
[TD]Female
[/TD]
[/TR]
</tbody>[/TABLE]

The output will be to order the data in numerical order by hourly rate and where the values are the same, this must be sorted in gender order 1 female then 1 male then 1 female etc and if there are none of difference then the same gender will populate until the next new number has been found.

The finished outcome will look like this

[TABLE="width: 204"]
<colgroup><col width="64" style="width:48pt"> <col width="76" style="width:57pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 76"]Hourly Rate[/TD]
[TD="width: 64"]Gender[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person D[/TD]
[TD="align: right"]8[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person E[/TD]
[TD="align: right"]8[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person F[/TD]
[TD="align: right"]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person G[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person J[/TD]
[TD="align: right"]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person H[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person I[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person K[/TD]
[TD="align: right"]7[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person L[/TD]
[TD="align: right"]6[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]Person N[/TD]
[TD="align: right"]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]Person M[/TD]
[TD="align: right"]6[/TD]
[TD]Male[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help?

 

[Eric W]

This works, but I sort of consider this to be a starting point, since the formulas are a bit awkward. One of them is an array formula, so it may well be slow on a sheet with thousands of rows. But I'll keep thinking about it, and maybe someone else might have a better idea. This might be better suited to a macro.

	A	B	C	D	E	F	G	H
Name	Hourly Rate	Gender	Name	Hourly Rate	Gender
Person A	Female	Person A	Female
Person B	Female	Person D	Male
Person C	Female	Person B	Female
Person D	Male	Person C	Female
Person E	Female	Person E	Female
Person F	Female	Person F	Female
Person G	Male	Person G	Male
9	7		7
Person I	Male	Person H	Male
Person J	Female	Person I	Male
7		7
Person L	Male	Person N	Female
Person M	Male	Person L	Male
Person N	Female	Person M	Male

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]Person H[/TD]

[TD="align: right"]Male[/TD]

[TD="align: right"]Person J[/TD]

[TD="align: right"]Female[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Person K[/TD]

[TD="align: right"]Male[/TD]

[TD="align: right"]Person K[/TD]

[TD="align: right"]Male[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=LARGE($B$2:$B$15,ROWS($F$2:$F2))[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=IF(COUNTIFS($F$1:$F1,F2,$G$1:$G1,IF(MOD(ROW()-MATCH(F2,$F$1:$F2,0),2)=0,"Female","Male"))< COUNTIFS($B$2:$B$15,F2,$C$2:$C$15,IF(MOD(ROW()-MATCH(F2,$F$1:$F2,0),2)=0,"Female","Male")),IF(MOD(ROW()-MATCH(F2,$F$1:$F2,0),2)=0,"Female","Male"),IF(MOD(ROW()-MATCH(F2,$F$1:$F2,0),2)=0,"Male","Female"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$15,SMALL(IF($B$2:$B$15=F2,IF($C$2:$C$15=G2,ROW($C$2:$C$15)-ROW($C$2)+1)),COUNTIFS($F$2:$F2,F2,$G$2:$G2,G2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

A shorter version of the G2 formula:

=IF(COUNTIFS($F$1:$F1,F2,$G$1:$G1,IF(MOD(COUNTIF($F$2:$F2,F2),2),"Female","Male"))< COUNTIFS($B$2:$B$15,F2,$C$2:$C$15,IF(MOD(COUNTIF($F$2:$F2,F2),2),"Female","Male")),IF(MOD(COUNTIF($F$2:$F2,F2),2),"Female","Male"),IF(MOD(COUNTIF($F$2:$F2,F2),2),"Male","Female"))

And still shorter if you use a helper column in H (Which you can hide):

G2: =IF(COUNTIFS($F$1:$F1,F2,$G$1:$G1,H2)< COUNTIFS($B$2:$B$15,F2,$C$2:$C$15,H2),H2,IF(H2="Female","Male","Female"))

H2: =IF(MOD(COUNTIF($F$2:$F2,F2),2),"Female","Male")

 

[Titanclaymore]

Many thanks for your speedy response and your step by step calculations this is amazing.