Returning Data from an array only if it meets certain criteria

I am trying to separate data from my name list into groups according to the age of the participant. I want to be able to upload my list and the data separate on the appropriate sheet automatically.

For example I have:

First Name Last Name AGE
Mitchell Zumbrunn 12.1
Brennan Vignati 10.3
Riley Vignati 8.0
Deja Tregre 6.5
Jordan Thompson 10.2
Liam Thompson 9.2
Marquise Thomas 13.1
Klesi Stubbs 7.5
Codey Strickland 13.9

on one sheet and the counselors' names on the other sheet. I would like to separate them out by age group with each counselor each time I upload a new list automatically. I can sort them by age but then I still have to copy and paste the names into the roster file. This would be fine if it weren't a ton of names (over 70).

If you would like an edited copy of the file (for privacy reasons) I can send it to you. Please help me. Thank you!

Jeff Y

Portuga,

Sorry, I should have included that. I would like to separate them into 5's 6's, 7-8's, 9-10's, 11-12's and Teens (13-17).

I have to confess that I am not that familiar with using pivot tables. I tried to use them to do the separation but apparently I was out of my league.

How do I use If statements to evaluate the entire list and return only the specific data without it being spread out all over the place?

Thank you for your help, I really do appreciate it!

Jeff Y

Originally Posted by Portuga

Hi,

It would help if you tell which are the age groups you want to devide the data into.

My suggestion then would be to use a helper column (using if statements) that would identify the age group they fall into and then use pivot tables, one for each tab, to automatically display the data.

Portuga,

Thank you for all your help. The formula you gave me worked great in giving me the groups that the kids are going to be in. Now I am looking up information on the pivot tables to transfer the data from that sheet to a list of names in the given age group. Thank you again for all of your help!

Jeff Y

Originally Posted by Portuga

I can help you out with the formula:
Something like this should do the trick:

=IF(AND(C2>=5,C2<=6),"5&6",IF(AND(C2>=7,C2<=8),"7&8",IF(AND(C2>=9,C2<=10),"9&10",IF(AND(C2>=11,C2<=12),"11&12",IF(AND(C2>=13,C2<=17),"Teens")))))

As for the pivot table, you can find a lot of documentation on the web to help you out.
I would suggest adding the new column to the page section of the pivot (filter here by the age group you want), the names of the student to the column section and "sum of age" to the data section.

Once you create a pivot you can copy and paste it to the other sheets, it will save you a lot of space as they will be all using the same cache, and select in the page section the age group you want.