Hi,
My wife helps in a school by listening to children read and produces a sheet of words for them to practise.
She adds new words a list and I've written a query to convert the list into a three-column table for each child (see attached). The query may be a bit over-complicated but it does the job.
I'd like to be able to split the resulting single table so that each child's words appear on a separate page so that I can simply print the entire table and get a single page for each child. In a standard pivot table I can subtotal on a value (eg child's name) and insert a page break at each point. I can't add a subtotal to the resulting table and wondered if there's a way to achieve this in Power Query?
As a bonus, I'd like to be able to be able to show the child's name in a single cell above their sheet, if possible.
I'd welcome any suggestions, please!
Many thanks,
Pieter
Hi Pieter,
You can use a PivotTable to automatically extract the words for each student into a new sheet using this technique. Beforehand, structure the PivotTable into a tabular format with no subtotals - see example attached.
Hope that helps.
Mynda
Hi Mynda,
Thanks for an elegant approach, which gets me a long way towards the outcome I want. Just one question: how do I prevent the pivot table from sorting the fields alphabetically? I'd like the words to appear in the same order as they appear in the source table, which ensures that any blanks appear at the end of each child's table. I know how to stop the pivot re-ordering after refresh, but that's not the same thing.
Many thanks.
Pieter
Hi Pieter,
You can't change the PivotTable sort order. If you don't have to print to paper, and can instead share the electronic copy of the file there are other options that will allow the students to filter the list to view only their words, for example, you can insert a Slicer for the name column.
Or if you have Microsoft 365 you can use the Filter function with a data validation list. See example file attached.
Mynda
Hi Mynda,
Thanks for some more great suggestions. I don't have 365 and, sadly, most of the students don't have access to computers and we need to provide a sheet of paper for each one to take home and practise.
Two things spring to mind - perhaps I should simply copy/paste special the table and use subtotal to insert a page break after each name - or do this with VBA - though I don't really need the subtotals to show.
However, perhaps I've made things over-complicated(!). The original requirement was to take a single-column list, sort it by name and use the result to fill a separate three-column table with each child's words.
I wonder if you might be able suggest something?
Thanks again, Mynda.
As ever,
Pieter
Hi Pieter,
I'd write some VBA to insert page breaks in the Power Query output table and print that. It's already in the format you need, you just need to set the print area and insert the page breaks, which VBA can do.
Mynda
Hi Mynda,
Sorry for the late reply - I hate to leave things dangling, but life got in the way!
Setting the print area and page breaks with VBA was a piece of cake, but I also wanted to use the slicer to select which table(s) to print with the name in the header. I finally achieved this by loading a dictionary with the slicer selection(s) and then using .Exists to drive the print output. I've attached the result for completeness.
Thanks again for your support.
Pieter
Congratulations, Pieter! Great to see your inventiveness with Excel 🙂
