Ordering data by highest number and gender per same number

Titanclaymore

New Member
Joined
Nov 30, 2013
Messages
15
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

NameHourly RateGender
Person A8Female
Person B8Female
Person C8Female
Person D8Male
Person E8Female
Person F7Female
Person G7Male
Person H7Male
Person I7Male
Person J7Female
Person K7Male
Person L6Male
Person M6Male
Person N6Female

<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

NameHourly RateGender
Person A8Female
Person D8Male
Person B8Female
Person C8Female
Person E8Female
Person F7Female
Person G7Male
Person J7Female
Person H7Male
Person I7Male
Person K7Male
Person L6Male
Person N6Female
Person M6Male

<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
Joined
Aug 18, 2015
Messages
8,959
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
Joined
Aug 18, 2015
Messages
8,959
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:

Forum statistics

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

Some videos you may like

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