Segregating Boys from Girls

Hello guys!


I need help on this workbook I'm working on.

In this file I uploaded for reference, I need to copy the names from Student Data (sheet) to Sheet 1. However, the students in the Student Data Sheet were not segregated between the boys and the girls and I wanted to segregate it on Sheet 1. Is there a formula in doing so? If not, what is the VBA code that would achieve this?

I'm really not familiar with VBA codes, and I would really appreciate it if you would help.. Thanks a lot!

Information Sheet.xls

Find the attached file

Please Login or Register  to view this content.

Change the Ranges 'Student Data'!$C$7:$C$41 & 'Student Data'!$G$7:$G$41 according to your needs like
if your data has 200 Rows then change the formula like
=IF(ISERROR(INDEX('Student Data'!$C$7:$C$2000,LARGE(INDEX(('Student Data'!$G$7:$G$2000="M")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$2000))),0),COUNTIF('Student Data'!$G$7:$G$2000,"M")-ROWS(B$12:B12)+1))),"",INDEX('Student Data'!$C$7:$C$2000,LARGE(INDEX(('Student Data'!$G$7:$G$2000="M")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$2000))),0),COUNTIF('Student Data'!$G$7:$G$2000,"M")-ROWS(B$12:B12)+1)))
for Boys and
=IF(ISERROR(INDEX('Student Data'!$C$7:$C$2000,LARGE(INDEX(('Student Data'!$G$7:$G$2000="F")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$2000))),0),COUNTIF('Student Data'!$G$7:$G$2000,"F")-ROWS(B$43:B43)+1))),"",INDEX('Student Data'!$C$7:$C$2000,LARGE(INDEX(('Student Data'!$G$7:$G$2000="F")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$2000))),0),COUNTIF('Student Data'!$G$7:$G$2000,"F")-ROWS(B$43:B43)+1))) for girls

Please Login or Register  to view this content.

and, as a rule, name the relevant column Gender, not ***

If you deleted some rows also It will work.

upload the file for which it is not working

see the attached file

Sir nflsales, i don't really get the difference between the 3 formulas.. are they really the same? I mean, if I was to make the other columns, what's the difference between the formulas?

Copy the formula and change the Column Number
for example
=IF(ISERROR(INDEX('Student Data'!$A$7:$AN$200,LARGE(INDEX(('Student Data'!$G$7:$G$200=IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),"F","M"))*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),"F","M"))-ROWS(G$4:G4)+1+IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),COUNTIF('Student Data'!$G$7:$G$200,"M"),0)),7)),"",INDEX('Student Data'!$A$7:$AN$200,LARGE(INDEX(('Student Data'!$G$7:$G$200=IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),"F","M"))*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),"F","M"))-ROWS(G$4:G4)+1+IF(ROWS(C$4:C4)>COUNTIF('Student Data'!$G$7:$G$200,"M"),COUNTIF('Student Data'!$G$7:$G$200,"M"),0)),7))
Gender in 7th Columns thats why i have enter 7 for Birth Date change it as 8.