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
- Make a copy of the sheet you’re working with. You don’t want to ruin your data accidentally.
- Click on the upper left hand corner, right click and choose copy. Â Right click again and choose paste special->paste values only.
- Sort the spreadsheet by the column with the unique identifier (so that all of the rows you want merged together are grouped together.)
- 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”
- Create a column to number the entries. In column K I did =if(C2=C1,K1+1,1)
- I used =max(K2:K300) to find out the most anyone had submitted the form.
- I created a column for each of these submissions. Â So since I had a max of 16, I created 16 extra columns.
- 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,””)
- 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. - 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,””),””)
- 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.
- Repeat until you have a column up to the maximum number of rows any person has.
- You will now drag each diagonal corner up to the first row. (Moving the formula).
- 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. - Sort by the row (K) which counted rows per person, you only need the row 1’s now.
- Delete all the extra rows.
- Can now run a mail merge with this merged data.












Sample Form:
You must log in to post a comment.