Jul 09 2020 10:33 PM
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?
Jul 11 2020 10:37 AM
@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?
Jul 11 2020 01:06 PM
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))
Jul 11 2020 01:42 PM
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
Jul 12 2020 03:01 PM
Jul 12 2020 06:16 PM
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.
Jul 12 2020 09:42 PM
You may also add predefined text in the formula suggested by @Sergei Baklan like this...
Assuming your data looks like this on Sheet1...
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...
Please refer to the attached for more details.
Jul 13 2020 06:19 AM
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 Smith | Academic Achievement Mathematics Academic Achievement Mandarin |
Jonathan | Academic 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 Smith | Academic 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.
Jul 13 2020 08:40 AM
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.