Feb 24 2015

## Google Sheets Lesson 6 – UNIQUE

So far so good. We want to send out students the assessments that their peers turned in, so we organized the data by student receiving feedback. However, in order to send the data out with an Add-On like Autocrat, we need to condense it so that there is only a single row of information for each students. In effect, we need to reorganize it so that each row represents a *student*, not a *feedback comment*.

To do this, we will use the function UNIQUE. This looks at a set of data and only returns rows which are unique. In our case, we will use it to filter all of the Sorted data and pull out only one student email entry for each given address.

We could have simply copy and pasted the list in there, but at times you are working with form entry data and you don’t know ahead of time what sort of information you will be collecting. This is an elegant solution that works in many circumstances.

Click on the link for Example 6, and look at the “Unique” sheet, cell A1. Think about other circumstances in which you may want to use it.

### Google Sheets Lessons 1-9

Click on a lesson below for a written tutorial demonstrating the lesson.

- Lesson 1 – CONCATENATE
- Lesson 2 – VLOOKUP
- Lesson 3 – ARRAYFORMULA
- Lesson 4 – formatting ARRAYFORMULA
- Lesson 5 – QUERY
- Lesson 6 – UNIQUE
- Lesson 7 – INDEX & MATCH
- Lesson 8 – conditional INDEX & MATCH
- Lesson 9 – Autocrat Add-on

Feb 24 2015

## Google Sheets Lesson 7 – INDEX & MATCH

In our last example we reorganized our data set by student email instead of by feedback comment. Now we want to pull the data from the Sorted sheet over into the Unique sheet, so that we have separate columns for Score 1, Eval 1, Score 2, Eval 2, etc.

Note that we

coulddo the first Score and Eval through a VLOOKUP, but wecan’tdo Score 2 or Eval 2 that way because that data is on the row below the VLOOKUP match.In order to fix this, we need to use a more complex, and more powerful, alternative to VLOOKUP called INDEX & MATCH.

Click here to open up Example 7 and look at the highlighted cell in the Unique tab.

INDEX & MATCH work together to get the job done. MATCH will return the relative row of a column of data that matches a specific value. MATCH takes 3 values:

MATCH(Search Key, Range, 0)

The 0 refers to a searchtype; normally we use 0 to indicate that it needs to be an exact match. Thus if our data set is

A12345The formula =MATCH(“student03@unishanoi.org”,A:A,0) will return a 4.

Now it’s time for the INDEX function. This will return the exact cell we need in a range (not just the correct column). It take three values as well:

For Range, we will enter in our whole search range, in this case

Next we enter in our MATCH formula, which will act as the Row offset:

We put this formula in the B2 cell, and it looks up the student email in A2, then returns the row that it finds that email in the B column of the Sorted sheet.

Finally, we have our Column offset, or the number of rows we want to count over from our range. Unlike VLOOKUP, where the initial column is counted as 1, here we start counting at 0. Thus, our the score value would be found with a column offset of 2.

We are left with this formula in cell B2:

=INDEX(Sorted!B:D,MATCH(A2,Sorted!B:B,0),2)

We can copy and paste this formula down for the whole B column, but we will need to adjust it for Columns C, D, and E.

For Score 2 and Eval 2, we will need to change the Row Offset from the MATCH value to the MATCH value + 1. For example:

For Eval 1 and Eval 2, we will need to change the Column Offset to 3 instead of 2.

This take a little practice, so take your time with it. Try to use the B2 formula as a basis to fill in the rest of the values.

## Google Sheets Lessons 1-9

Click on a lesson below for a written tutorial demonstrating the lesson.

## Examples / Challenges

By pswanson • Tutorial • 2