I was asked to provide a data sheet for the NWEA assessment.
Interestingly, NWEA provides two spreadsheets with the necessary information. The first sheet (NWEAWinter20) is quite a lengthy sheet with all the testing information. This sheet includes testing dates, time on task, school, and the breakdown of all the testing. The second sheet contains the student information. So the first task is to copy the Student information onto a new tab in the Assessment sheet. Now we have all of the information on one spreadsheet (although two tabs).
Next, combine the student information with the assessment data. Good news is that since StateIDNumber is on both, this means a “simple” VLOOKUP.
I added five blank columns in order to merge the five data columns that I needed (StudentLastName,StudentFirstName, StudentMI, Grade, and StudentDateOfBirth). I left the first two columns (StateStudentID, and StudentID) in place. This allows me to use the first column for the LOOKUP. In cell C2, I added the following formula:
=VLOOKUP(A2,Students!$A$2:$G, 3,0)
The data that we want to copy over lives in columns C, D, E, F, and G. The data that we are matching (StateStudentNumber) is in column A. VLOOKUP’s must run off the first column. (* You can do similar things with different commands if you can’t use the first column.)
Let’s take a quick look the formula. We always start with an “=” to indicate a formula. “VLOOKUP” is our magic formula. This is a vertical lookup; meaning that the spreadsheet will vertically (thus the V).
Next, the A2 says to look at cell A2 to find a match. Thus, the StateIDNumber in A2 will be used to find the student on Student tab. Next, we have “Students!$A$2:$G”. Let’s break this one down. Since we are pulling the data from a different tab, we need to say which tab that is. Thus the Students!. Next, we add the range on that tab that we pulling from: A2:G. But, what about the $? We want to use absolute rows, thus the $. This means that the spreadsheet will always look through the whole column (note that we don’t close the G column so that we don’t risk “cutting data off”).
The next number maps to the column that we need. In this case, we are looking for the information in Column C (which is the third column, thus 3). Basically, wherever the information in cell A2 on our current sheet, matches the information in Column A on Student tab, the information in Column C (last name) will be inserted in this cell.
Finally, the last argument is a “0” meaning that we don’t have a header row. * We do have a header row, but we have skipped this in both spots for ease of use.
As we copy this formula down the column, the next row will change from:
=VLOOKUP(A2,Students!$A$2:$G, 3,0)
to
=VLOOKUP(A3,Students!$A$2:$G, 3,0)
The only thing that is changing is the first argument (A2 becomes A3) which is referencing the StateStudentNumber for the next row.
For our First Name column (Column D), we use the same process, but change the Column imported to 4. Thus, the formula for cell D2 becomes:
=VLOOKUP(A2,Students!$A$2:$G, 4,0)
Next, comes StudentMI (Column E). The formula for cell E2 becomes:
=VLOOKUP(A2,Students!$A$2:$G, 5,0)
Grade is our next column (Column F). You guessed it:
=VLOOKUP(A2,Students!$A$2:$G, 6,0)
Finally, Birthdate (Column G).
=VLOOKUP(A2,Students!$A$2:$G, 7,0)
Now we have all the students combined with all the assessment data. Cool! However, there is tons and tons of data here. There is a mix of Mathematics and Language Arts.
The request was to create a tab with selected information:
- State ID
- Student ID
- LastName
- FirstName
- MI
- Grade
- DOB
- RIT
- Percentile
- Quartile
- Lexile
- Goal 1
- Goal 1- RIT
- Goal 2
- Goal 2 – RIT
- Goal 3
- Goal 3 – RIT
- Goal 4
- Goal 4 – RIT
- Goal 5
- Goal 5 – RIT
Still lots of information, but much more focused.
So, we create a new tab. We name the tab “Math”. We now have three tabs: NWEAWinter20, Students, and Math.
Now we need a formula to pull just the Math information onto the “Math” tab.
Here’s our formula:
=QUERY(FILTER(NWEAWinter20!A2:ET,NWEAWinter20!M2:M=”Mathematics”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″)
Again, let’s break this down. We need a QUERY to pull the information from another sheet. We need a FILTER to, well, filter the information. The FILTER allows us to pick the tab (NWEAWinter20) and use the Column M (which is Subject: either “Mathematics” or “Language Arts”) to limit the results to that subject. This will only pull the test results that are Mathematics.
The SELECT says to just bring over the columns that we need. The first seven columns are demographic information, so we pull all of those. Next, we have to convert Columns to Numbers. Better Solutions had a terrific conversion chart. This tells me that Column AC is 29. The list of Columns pulls those columns listed above.
The last part is WHERE. The request was limited to grades 5-8. Column F(Col6) listed the grade of the student. Since I wanted grades 4-8, I used “L>3 AND L<9”.
Now that we have this set, we can do the same thing for Language Arts. Since we have the hard work done, we simply duplicate the “Math” tab, rename that tab “Language Arts”. Then we change the formula in cell A2 from:
=QUERY(FILTER(NWEA_Spring_2024!A2:ET,NWEA_Spring_2024!M2:M=”Mathematics”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″) to =QUERY(FILTER(NWEA_Spring_2024!A2:ET,NWEA_Spring_2024!M2:M=”Language Arts”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″)
Simply changing the Filter criteria after the M2:M from “Mathematics” to “Language Arts” means that we are done.
Now we are ready to slice and dice the information even more. We can now utilize Pivot Tables, Filtering and more.
