Segregating Boys from Girls

**wedzmer** · 07-10-2014, 02:04 AM

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

**samba_ravi** · 07-10-2014, 03:01 AM

Find the attached file

**wedzmer** · 07-10-2014, 03:31 AM

Hi nflsales!!!

Thanks a lot!

Quick question before I mark the thread solved... Would the same formula you used in Sheet 1 work if the Students Data Sheet varies in number of Boys and Girls..?For example if the boys had 40 students and the girls had 18, so the range of the cells would adjust in Students Data Sheet.

**samba_ravi** · 07-10-2014, 03:45 AM

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

**dubcap01** · 07-10-2014, 04:09 AM

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

**wedzmer** · 07-10-2014, 04:14 AM

Change the Ranges 'Student Data'!$C$7:$C$41 & 'Student Data'!$G$7:$G$41 according to your needs like

Would dragging the arrow work? I'm afraid that would give me error...

and I noticed you placed a formula on cell B8:

=SUM(COUNTIF(OFFSET('Student Data'!$G$7,0,0,ROW(1:35),1),"F"))

Would something if I deleted this?

And Hello DubCap01,

What do you mean by

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

?

**samba_ravi** · 07-10-2014, 04:36 AM

If you deleted some rows also It will work.

**wedzmer** · 07-10-2014, 05:00 AM

Originally Posted by nflsales

If you deleted some rows also It will work.

I see...
But the formula won't work when combined...

I mean, i tried to manipulate the formula to make a new Student Information Sheet referencing from the old one... But it won't work...

**samba_ravi** · 07-10-2014, 05:02 AM

upload the file for which it is not working

**wedzmer** · 07-10-2014, 06:01 AM

I tried to make a replica of the Student Data Sheet on Sheet2.... I wanted to reference the same thing using the said formula but this time, combining the Male and Female.

here's the formula I tried to manipulate with:

=IF(G10="M",IF(ISERROR(INDEX('Student Data'!$C$7:$C$200,LARGE(INDEX(('Student Data'!$G$7:$G$200="M")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,"M")-ROWS(B$12:B12)+1))),"",INDEX('Student Data'!$C$7:$C$200,LARGE(INDEX(('Student Data'!$G$7:$G$200="M")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,"M")-ROWS(B$12:B12)+1))),IF(ISERROR(INDEX('Student Data'!$C$7:$C$200,LARGE(INDEX(('Student Data'!$G$7:$G$200="F")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,"F")-ROWS(B$43:B43)+1))),"",INDEX('Student Data'!$C$7:$C$200,LARGE(INDEX(('Student Data'!$G$7:$G$200="F")*ROW(INDIRECT("1:"&ROWS('Student Data'!$G$7:$G$200))),0),COUNTIF('Student Data'!$G$7:$G$200,"F")-ROWS(B$43:B43)+1))))

Here's the new file I wanted to use it.. but gives me error...

Information Sheet_1.xls

**samba_ravi** · 07-10-2014, 06:56 AM

see the attached file

**wedzmer** · 07-10-2014, 07:49 AM

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?

**samba_ravi** · 07-10-2014, 08:33 AM

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.

**wedzmer** · 07-10-2014, 09:27 AM

hahaha! So that's the difference!
Thanks nflsales!!!

Segregating Boys from Girls

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Re: Segregating Boys from Girls

Thread Information

Users Browsing this Thread

Similar Threads

Finding and Segregating Outliers

Boys versus Girls

Segregating Hyperlink & HyperText - Please Help

Bookmarks

Bookmarks

Posting Permissions