How can I copy rows based on specific data values?

Hello all,

I am looking for a way to extract and copy all rows that have a specific value in column C.

To be more specific, I have a data set that looks like the following

Name Grade Class Score 1 Score 2 Score 3
Student 1 6th A 90 95 80
Student 2 7th B 100 100 100
Student 3 7th A 75 90 95
Student 4 6th A 85 85 100
Student 5 8th B 95 70 95
Student 6 6th A 100 80 95
Student 7 8th B 95 70 85
Student 8 8th A 95 80 85

The actual data set is much larger, but it looks like the example above.

What I am trying to do is add a set of rows to dissagregate the data by separating it into students in class A and students in class B. Once it is dissagregated, I want the entire sheet to look as follows:

Name Grade Class Score 1 Score 2 Score 3
Student 1 6th A 90 95 80
Student 2 7th B 100 100 100
Student 3 7th A 75 90 95
Student 4 6th A 85 85 100
Student 5 8th B 95 70 95
Student 6 6th A 100 80 95
Student 7 8th B 95 70 85
Student 8 8th A 95 80 85


Dissagregated
Class A:
Name Grade Class Score 1 Score 2 Score 3
Student 1 6th A 90 95 80
Student 3 7th A 75 90 95
Student 4 6th A 85 85 100
Student 6 6th A 100 80 95
Student 8 8th A 95 80 85
Averages

Class B:
Name Grade Class Score 1 Score 2 Score 3
Student 2 7th B 100 100 100
Student 5 8th B 95 70 95
Student 7 8th B 95 70 85
Average

Here's the catch: The spreadsheet is both huge and dynamic, so I am trying to find a way to have excel do this automatically as data is entered, rather than manually sorting it every time changes are made.

I am very familiar with formulas, but I have literally no experience with macros or programming. I tried searching through and reading over lots of other posts, but have not had much luck. Any help would be greatly appreciated.

Thanks ahead of time.

Awesome! Thanks a lot! Quick follow up question...

Ive set up the worksheet to have the dissagregated rows below the initial rows so that the worksheet looks as follows:


Name Grade Class Score 1 Score 2 Score 3
Student 1 6th b 100% 95% 80% b1
Student 2 7th a 100% 100% 100% a1
Student 3 7th b 75% 90% 95% b2
Student 4 6th a 85% 85% 100% a2
Student 5 8th a 95% 70% 95% a3
Student 6 6th b 100% 80% 95% b3
Student 7 8th b 95% 70% 85% b4
Student 8 8th b 95% 80% 85% b5

Dissagregated by Class
Class A Averages: 93% 85% 98%
Student 2 7th a 100% 100% 100%
Student 4 6th a 85% 85% 100%
Student 5 8th a 95% 70% 95%





Class B Averages: 93% 83% 88%
Student 1 6th b 100% 95% 80%
Student 3 7th b 75% 90% 95%
Student 6 6th b 100% 80% 95%
Student 7 8th b 95% 70% 85%
Student 8 8th b 95% 80% 85%

Naturally there are spaces in between Class A and Class B since I have to leave enough room for all students, but not all spaces will neccesarily be filled. Is there a way to use conditional formatting to have excel automatically shrink all empty rows to size zero so that the large gaps do not appear? If not, any other recommendations?

Thanks ahead of time again

Thanks again for the suggestion. It works really well as long as I filter each time new data is entered, but is there any way I can make it automatically filter each time data is changed for example if students switch classes?

Quick follow up question as well...This formula copies all blank cells as zeros instead of null strings.

The difficulty with this is that unless a score is entered the averages in the dissagregated area are skewed. For example, if out of 5 students only one student's score is entered (For example, 100%), the sheet will read all other scores as zeros and report an average of 20%.

I have used a formula similar to =IF(A1="","",A1) in the past to deal with this issue, but I am not sure how to integrate it into the formulas used to dissagregate data.

Any suggestions?

Thanks again.

Regarding formula to calculate average (=SUM(D2:D20)/COUNTIF(D2:D20,">0")), what if a student is having ZERO marks ? His average will go up if we use this formula ---> Control issue.