Good morning,
I so hope someone can point me in the right direction. I work in a school and every student who has been given a commendation this Semester will receive a certificate of their achievements. Many students have multiple commendations. I know from the pivot table I created that 3 students have received 10 separate commendations each.
I have been asked to produce a file to use a as a mail merge document where every has one row and their achievements are listed across this row using the Headers in the sample spreadsheet. (Unless someone can think of a better way to mail merge all this).
Where on earth do I start, I have no idea how to transform this data.
The attached file is a small sample (there are over 3000 lines) but I have changed the student and teacher names.
I'm hoping this is an opportunity for me to learn how to do something new.
Thank you
Becky
You could begin by clarifying how such a certificate should look like. Especially one with multiple commendations. Depending on that, you could use Power Query to group the data by student and combine certain columns into one.
Thank you for taking the time to reply Riny. 
You're welcome. Have you resolved it now?
Hi Riny,
No not really, I'm just trying things to see what works. So far, I have created a pivot table with the data I have, so I have grouped the students per awards given. It's a start. The problem is 'you don't know what you don't know' and there is literally no-one to ask for help here.
All part of the learning curve I guess, I'll keep trying
At this rate it would be quicker to handwrite the darned things myself
Thanks again
But please answer my question with regard to how the certificates look like, I'm sure it's relatively easy to resolve. So if you can post an example of a certificate for a student with multiple achievements, it would help a lot.
One example certificate attached. Student name at the top and the teachers names below.
That's great!
I've transformed the data table with Power Query so that the Subjects, Teacher names and Assessments fit in a single cell with line feeds. The resulting table can then be easily used in Mail Merge. I've attached bot the XLSX and DOCX files. See if you can implement this with your real data.
Hi Riny,
I'm back from a two week school holiday break. Thank you taking the time to run this simulation, now I know what configuration to aim for. I'm very new to Power Query.
Thank you
