Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Many to One

Many to One

This school year I am trying to do my grading with a pivot table.  I am using mail merge to send students individualized feedback and their grade. What I haven’t quite been able to get nailed down is how to give students a list of their assignments in a paperless way. I could import the spreadsheet to a database and easily create reports per kid… but I’m trying to stay in Google.  I saw a plea go out on a list serve the other day for wanting to merge multiple rows to one row and I got a phone call the next day of someone asking me the same thing. So clearly I am not the only one in need of this ability.  Searching for scripts or on Google for solutions came up empty.  Ended up resorting to a complicated formula. So here is what I did… probably not for the faint of heart.  If you’re not fairly proficient with a spreadsheet I wouldn’t attempt it.  Try this blog post on pre-populating a Google Form instead.

First I have students fill out a Google Form that is embedded in my website to turn in all assignments.  (Sample form is embedded at the bottom, feel free to fill it out).

Here is link to my sample spreadsheet: http://goo.gl/YBCvS

  1. Make a copy of the sheet you’re working with. You don’t want to ruin your data accidentally.
  2. Click on the upper left hand corner, right click and choose copy.  Right click again and choose paste special->paste values only.
  3. Sort the spreadsheet by the column with the unique identifier (so that all of the rows you want merged together are grouped together.)
  4. Insert a row for concatenation.  I can not actually merge all the rows together, so I need to get all the data from the single row into one cell.  I did =”#”&E2&” “&F2&” earned “&G2&” points”
  5. Create a column to number the entries. In column K I did =if(C2=C1,K1+1,1)
  6. I used =max(K2:K300) to find out the most anyone had submitted the form.
  7. I created a column for each of these submissions.  So since I had a max of 16, I created 16 extra columns.
  8. For the column that would contain the information from the first row, that is easy, I just said it equals what is in my concatenation column (J). =if(K2=1,J2,””)
  9. Not everyone has a row 2 so I need to check if they do and then get the information from row 2’s concatenation cell. I did =if($B3=$B2,if($K3=M$1,$J3,””),””)
    I am checking if the unique identifier for the rows are identical.
  10. Unfortunately you have to build each formula for each column almost by scratch.  I did a little cheating with that, but could not get away from editing each cell individually at least a little bit. Next row and column would be =if($B4=$B3,if($K4=N$1,$J4,””),””) and the next column =if($B5=$B4,if($K5=O$1,$J5,””),””)
  11. If you did it correctly it is always checking one row below the first unique row for a person, 2 rows below the first row for that person, 3 rows below the first row for that person, etc… and putting in the one cell that contains all the information for that row.
  12. Repeat until you have a column up to the maximum number of rows any person has.
  13. You will now drag each diagonal corner up to the first row. (Moving the formula).
  14. Click on the upper left hand corner, right click and choose copy.  Right click again and choose paste special->paste values only.
    This will allow you to now sort your data without messing up your formulas.
  15. Sort by the row (K) which counted rows per person, you only need the row 1’s now.
  16. Delete all the extra rows.
  17. Can now run a mail merge with this merged data.
Notice same student, multiple rows. Students are not grouped together.
Copy sheet so you can edit everything.
Need to know how many rows each student submitted. Write a formula to count.
Need one cell that has all the row data you want. Use concatenation.

 

Need one column per row. Temporarily name them 1, 2, 3, etc...
If this is row 1, copy the concatenate field
Go to row 2 and write formula that checks if it belongs to the same person and also checks if it is the 2nd row. Use cell referencing.
Notice you are building this formula diagonally. You can/should drag the formula from row 2 down and across. Color code the diagonal formulas, those are the ones you need.
Notice this first student does not have 4 assignments, so it came out blank. This is good, formula is working. Need this formula, so color code the 4th row "4th" column.
Grab diagonal cells and drag them up to the 1st row.
Pull down the formulas for row 1. Notice only the 1st row for each person has the row data.
Need to paste special the page so only values are in each cell, remove the formulas. Sort the data by row so all the 1st rows are grouped together. Delete all the extra rows.

 

 

 

 

Sample Form:

 

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.