Log In
Register
Home
Active Member
Power Query
November 19, 2020
Offline
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
Moderators
January 31, 2022
Online
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.
Active Member
Power Query
November 19, 2020
Offline
Thank you for taking the time to reply Riny. 
Moderators
January 31, 2022
Online
You're welcome. Have you resolved it now?
Active Member
Power Query
November 19, 2020
Offline
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
Moderators
January 31, 2022
Online
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.
Active Member
Power Query
November 19, 2020
Offline
One example certificate attached. Student name at the top and the teachers names below.
Moderators
January 31, 2022
Online
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.
Active Member
Power Query
November 19, 2020
Offline
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
1 Guest(s)
