School Presentation Day Data: Help save me endless hours of typing

Question

I recently started working in a school and I'm finding our processes are a little dated. Presentation day prep for end of year is so huge and I spent days typing everything out last year, we need a better system. Let me explain and hopefully someone has inspiration.

Current process:

Teachers input into a spreadsheet the awards students are winning by entering a '1' into th relevant column.

e.g John Smith receives a Maths & Mandarin award

An admin person (me...) will then go into every sheet and every student (400+) and physically type out in a Word document:

I know there has to be a better way. Whether it's a formula working with the existing format that reads a 1 in a column and changes it to the right text, or if it's reimagining the entire process to work better. But my brain is a little fried from mulling this one over and need some help from the community.

Thoughts?

sergeibaklan · Answer

leanne8&nbsp;
As variant that could be combined

by
=TEXTJOIN(CHAR(10),,IF(INDEX($D$3:$O$5,MATCH(B8,$B$3:$B$5),0)="","",$D$2:$O$2))

theantony · Answer

leanne8&nbsp;, this sounds like a use case for Power Query. Can you upload some samples (with any confidential information removed) so it's easier to provide a solution?

mathetes · Answer

leanne8&nbsp;&nbsp;When you talk of the "endless typing" I'm hearing that as some form of document, whether a certificate or a citation of some kind, and that brings up the possibility of using Excel as the data source, and Word as the document processor. Word has the ability to take data from Excel and merge it into a document that has x amount of "boiler plate" text and space for the individual's name and achievements, as well as other tailored, even conditional, text.&nbsp;Here's an introductory YouTube video...and it's very basic. https://www.youtube.com/watch?v=mFqCvTOpOL0&nbsp;&nbsp;It's not hard to use

leanne8 · Answer

Thanks for your suggestion. I'm aware of how to mail merge as I use this often. Unfortunately MM won't solve this one as I need it to be smarter and turn the '1' in a particular column into a full title of the achievement.

mathetes · Answer

leanne8&nbsp;&nbsp;It looks to me as if SergeiBaklan&nbsp; has given you a very viable solution. Come back if that still leaves something to be desired, but he certainly delivered what you asked for. It's possible you still might want to combine it with MailMerge to produce a nice looking certificate....but that's another matter entirely.

Forum Discussion

School Presentation Day Data: Help save me endless hours of typing

8 Replies