# Ordering data by highest number and gender per same number

#### Titanclaymore

##### New Member
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

##### MrExcel MVP
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.

ABCDEFGH
1NameHourly RateGenderNameHourly RateGender
2Person A8FemalePerson A8Female
3Person B8FemalePerson D8Male
4Person C8FemalePerson B8Female
5Person D8MalePerson C8Female
6Person E8FemalePerson E8Female
7Person F7FemalePerson F7Female
8Person G7MalePerson G7Male
9Person H 7MalePerson J 7Female
10Person I7MalePerson H7Male
11Person J7FemalePerson I7Male
12Person K 7MalePerson K 7Male
13Person L6MalePerson N6Female
14Person M6MalePerson L6Male
15Person N6FemalePerson M6Male

<tbody>
</tbody>
Sheet9

Worksheet Formulas
CellFormula
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
CellFormula
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>

Last edited:

#### Eric W

##### MrExcel MVP
A shorter version of the G2 formula:

Code:
``=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")

Last edited:

#### Titanclaymore

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

1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...