Ordering data by highest number and gender per same number

T

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

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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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
NameHourly RateGenderNameHourly RateGender
Person AFemalePerson AFemale
Person BFemalePerson DMale
Person CFemalePerson BFemale
Person DMalePerson CFemale
Person EFemalePerson EFemale
Person FFemalePerson FFemale
Person GMalePerson GMale
9 7 7
Person IMalePerson HMale
Person JFemalePerson IMale
7 7
Person LMalePerson NFemale
Person MMalePerson LMale
Person NFemalePerson MMale

<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]
 
Last edited:
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pull data from sheet to new sheet based on multiple criteria from another sheet in excel
Replies
2
Views
361
sallam Qasem
S
M
  • Solved
Calculate the headcount at the end of each month based on the start/leave dates, split by employee type and country.
Replies
5
Views
894
Fluff
Fluff
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
581
harzer
H
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top