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

Copper Contributor

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

leanne8_0-1594358817656.png

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

leanne8_0-1594358952282.png

 

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?

8 Replies

@leanne8 , 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?

@leanne8 

As variant that could be combined

image.png

by

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

@leanne8 

 

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.

 

Here's an introductory YouTube video...and it's very basic. https://www.youtube.com/watch?v=mFqCvTOpOL0

 

 

It's not hard to use

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.

@leanne8 

 

It looks to me as if @Sergei Baklan  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.

@leanne8 

You may also add predefined text in the formula suggested by @Sergei Baklan like this...

 

Assuming your data looks like this on Sheet1...

School Data.jpg

 

Then on Sheet2, you may have a list of all the students names in column A starting from cell A1 and place this formula in B1 and confirm it with Ctrl+Shift+Enter.

=TEXTJOIN(CHAR(10),TRUE,IF(INDEX(Sheet1!$C$2:$K$5,MATCH(A1,Sheet1!$A$2:$A$5,0),)=1,"Academic Achievement " &Sheet1!$C$1:$K$1,""))

 

And the formula output would be like this...

Certificate.jpg

 

Please refer to the attached for more details.

 

 

 

 

@leanne8 

So long as we're fine-tuning what can be done, I'd recommend not "hard-coding" the words "Academic Achievement" into the formula. Rather, have a cell into which you can enter and fine-tune the wording, and have the formula automatically pick it up. This would give you maximum flexibility.

Desired Text>Academic Achievement
  
  
  
John SmithAcademic Achievement Mathematics
Academic Achievement Mandarin
JonathanAcademic Achievement Academic Effort
Academic Achievement English
Academic Achievement Science and Technology

See the attached for an example. I've used cell $B$1 on the "Report" sheet...that text can be changed to your heart's content.

Right now it reads simply "Academic Achievement" but it can be changed to

Academic Achievement in Kindergarten

  or

Desired Text>Academic Achievement:
  
John SmithAcademic Achievement: Mathematics
Academic Achievement: Mandarin
 

Note the colon in this last iteration.

 
 

Whatever you want. In general, it's a good idea to avoid hard-coding text (or other variables) into formulas.

@leanne8 

 

One final tweak here. In so much as I'm still assuming you want to be able to print nice certificates of some kind on an individual basis. All of the results so far gave you the text for the citations, but all as a list of all the names......

 

Here's a variation--still using the formula that @Sergei Baklan  first posted--that produces individual reports. You will need to use a drop-down box to select the name, and still do it one at a time, but it is, as the saying goes, "Suitable for Printing."  And you can extend the database as much as desired; it should all grow and be handled by the formulas...

 

A PDF of the output, as well as the generating spreadsheet is attached.