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

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

<colgroup><col width="64" style="width:48pt"> <col width="76" style="width:57pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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

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

<colgroup><col width="64" style="width:48pt"> <col width="76" style="width:57pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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
1	Name	Hourly Rate	Gender		Name	Hourly Rate	Gender
2	Person A	8	Female		Person A	8	Female
3	Person B	8	Female		Person D	8	Male
4	Person C	8	Female		Person B	8	Female
5	Person D	8	Male		Person C	8	Female
6	Person E	8	Female		Person E	8	Female
7	Person F	7	Female		Person F	7	Female
8	Person G	7	Male		Person G	7	Male
9	Person H	7	Male		Person J	7	Female
10	Person I	7	Male		Person H	7	Male
11	Person J	7	Female		Person I	7	Male
12	Person K	7	Male		Person K	7	Male
13	Person L	6	Male		Person N	6	Female
14	Person M	6	Male		Person L	6	Male
15	Person N	6	Female		Person M	6	Male

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula F2 =LARGE($B$2:$B$15,ROWS($F$2:$F2)) G2 =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")) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=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)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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.