Student Data Sheet

*Warning, what follows may not be the most efficient or elegant solution to the problem noted. However, it is a solution.

In education, we frequently want to look at multiple data points for a single student. Often these are standardized tests. Looking at the data from a single vendor for a single assessment is usually quite easy. The vendor creates tons of reports. However, combining different results is usually a bit harder.

My first “go-to” is usually a pivot table. Pivot tables will provide great flexibility. However, the end-users (here administrators and teachers) aren’t always comfortable manipulating pivot tables. It can be more confusing rather than enlightening.

So, let’s take a look at what the educators are looking for. Basically, a sheet with a student name, then different assessment results on one line.

I started by exporting a copy of student data with the fields that I need (Student State ID, Student Number, Student Last, Student First Name, Teacher, Grade Level). (This is the information identified as necessary by the school as the end-user. They want teachers to be able to see their students. I added the Student State ID field as an identifier that was common amongst the reporting spreadsheets). I named this Student Data Example. Then I renamed the tab DataView (skipping spaces makes formulas easier).

Next, I added the assessments that we are going to look at (as determined by the school). I also color-coded the assessments.

Now we’re ready for some data.

I’ve exported the NWEA data as a csv file (spreadsheet). Then I looked at the NWEA data. I moved the State ID field to be the first field on the spreadsheet. This is necessary to match the records through a VLOOKUP.

I copied the data to a new tab on the Student Data Example spreadsheet. (*I could’ve done an IMPORTRANGE – which would automatically copy the data, but maybe later). Now I’ve got a spreadsheet with two tabs: DataView (the tab that users will look at) and Fall_NWEA (where I’ve copied the NWEA results for Fall).

Normally, this would be a simple VLOOKUP. However, NWEA has multiple tests per student. That is, a student has probably taken three tests. Each one is a row. Using VLOOKUP would grab the first result and move on. Thus, I couldn’t get the reading score in the reading box for sure. What I need is just the Reading (just the Math, and just the Language) scores on one table.

I created a new tab on the spreadsheet and titled it Fall_NWEA_Reading. I copied the header row (only A through N as those are the columns with data that I need). Then in cell A2 I wrote the following formula:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Reading”)

What this formula does is to copy the data from the Fall_NWEA tab where the information in Column G is “Reading”. This means that I get one result per student that is their Reading score. This is exactly what I need.

Next I duplicate that tab, change the name to Fall_NWEA_Math. Then I click in cell A2 and change the formula from:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Reading”)
To:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Math”)

Now this tab will pull all the data from the Fall_NWEA tab that has the word Math in column G.

Then I repeat that process (duplicate the tab, rename to Fall_NWEA_Language, and change “Reading” to “Language”). Now I have a discrete cell that I can reference on the Student Data sheet.

Back to DataView tab. Under each of the tests, I can now reference a specific cell through VLOOKUP. For example, in the Fall NWEA Math Test Percentile column, I can add the following formula:

=VLOOKUP(A2,Fall_NWEA_Math!$A$2:$M,13, FALSE)

Here are the guts:

• VLOOKUP is the command.
• A2 references the cell to compare
• Fall_NWEA_Math!$A$2:$M- this is the tab to look at (remember it will use the first column on the tab to compare – the $ signs are necessary to lock the formula so that it will search all the way down
• Column 13 is the column (M) with the data that I want.

Essentially, this looks at the information in cell A2 on the DataView sheet. Then it looks through the first column of the tab Fall_NWEA_Math for a matching result. If it finds one, it can use the data from that row (A through M because that is where I told it look). The next part of the formula (13) says put the data in the cell here. The FALSE means look for an exact match.

Now you can fill that formula down to all the rest of the rows.

Next we’ll grab the Fall NWEA Math Test AchievementQuartile. In the next column, we’ll pretty much copy that formula, but adjust for the appropriate column were the results that we want are. Since I want the data that is in column N, I have to expand two parts – I need to pull data from A-N and I want column 14. Thus:

=VLOOKUP(A2,Fall_NWEA_Math!$A$2:$N,14, FALSE)

That’s it. Now that I have clean usable data, I can repeat this basic process.
Next, we’ll use conditional formatting to highlight the scores per colum, but that’s another post.