How do I do this efficiently?

[ERC2]

I have a large spreadsheet with students' test scores since 2006. I wanted to find out how 10th graders who were in our system in our system since 4th grade did. Thus I want I subset of our group of 2012 10th grade students. The spreadsheet has student info and then grade, the date the test was given and then the score by column. Thus a student will have multiple rows of data (one for each year in the system). The way I solved the problem was through a pivot and then filtering. What I did was get each year lined up as a column and the students as rows in the pivot. I cut and pasted the original pivot data and filtered everyone out who did not have a score in each year. I can't imagine there is not some better way to do this.

I really do not want to change the file format b/c this is the way we get the data from the state and I am dealing with a very large spreadsheet. Maybe that's the reason to change it though?
Thanks in advance!

 

[Skybluekid]

So I am correct in saying that you want to compare all the students' scores that were in the 4th grade against the scores they have attained in the 10th grage? Are all the years on the same worksheet?

 

[ERC2]

So I am correct in saying that you want to compare all the students' scores that were in the 4th grade against the scores they have attained in the 10th grage? Are all the years on the same worksheet?

Skybluekid, THanks for the reply. I want to get all those grade 10 students who were in our school system in grades 4-10 (as opposed to students who had only been in for a few years.). That is not exactly the same as the group who took the grade 4 test and the grade 10 test. Everyone is in one giant spreadsheet. If a student had been in the system since 2011, they would have two rows of data (2011 & 12). If they have been in since grade 4 they would have 2006, 2007, 2008, 2009, 2010, 2011, and 2012 or 7 rows.

 

[Skybluekid]

Can you send a dummy sample of data? What version are you using?

 

[Skybluekid]

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
2		Year Started	Results
3			Grade 4	Grade 5	Grade 6	Grade 7	Grade 8	Grade 9	Grade 10
4	Student 1	2006	67	76	74	64	80	81	86
5	Student 2	2006	57	65	78	77	87	82	83
6	Student 3	2006	40	56	60	63	75	79	78
7	Student 4	2006	87	83	84	86	81	80	81
8	Student 5	2007		67	78	78	69	75	78
9	Student 6	2007		75	78	71	75	75	78
10	Student 7	2009				89	91	87	85
11	Student 8	2008			76	78	81	84	87
12	Student 9	2010					67	76	76
13	Student 10	2006	69	78	76	73	75	81	81
14
15	5
16				Student 1	86
17				Student 2	83
18				Student 3	78
19				Student 4	81
20				Student 10	81
21
Sheet1

 

[ERC2]

Thank you Skybluekid. I need to study up on arrays. I thought of another more pedestrian way of doing it with Pivots but it is still a lot of steps. Your formulas take time up front (once I figure them out) but then they are done. Nice work & thanks!