About two years ago, a visitor to my networking group, BSI Dunmow, spoke to me at some length about the work I do, specifically asking for some ideas on a complex mailmerge that she was doing using Microsoft Word. She comes from a fair way out of the area, so decided not to join us, but kept my card and recently got in touch again.
She produces legal documents for a solicitor which, up to now, she has been producing in Microsoft Word. These include a schedule of work done (stored as raw data in a Word table), then merged documents to produce a fair copy of the schedule, with a breakdown of total costs at the end typed in by hand, and individual information sheets for each item on the schedule and for each item in the totals. She also had a set of equivalent files for personal attendances.
Recently, it has been decided in the practice that they need to analyse the costs of items that fall into different categories – information that was not even in the raw data document. She rang me to talk about this, and I felt that now would be a good time to move the data part of the requirement into Excel so that it can be analysed.
Excel is somewhat lacking as a word processor, but I found I was able to structure the “raw data” neatly enough to be able to print out in the same way as her original merged schedule and attendance documents. With the help of some pivot tables, and some careful text manipulation using functions, I was able to add the breakdown of total costs at the end of each report, and another pivot table gave the category analysis. Finally, I tweaked her original merge document and got it to reference the new spreadsheet as the data source to produce the item-by-item pages.
There was one disproportionally difficult part of this. Because the work is done for a legal firm, dates and times are separated by full stops, not slashes and colons as they are in Windows. Formatting these in Excel was easy enough, but the number formatting in Word doesn’t work in the same way and it took me a while to work out the correct formatting for times – especially when I wanted to total time and it tried to show the result as days and hours!
