Mail Merge to create timesheets
April 7, 2020 8:34 AM   Subscribe

I have an Excel spreadsheet showing time worked for around 70 employees. Each employee has between 7 and 14 entries. I would like to do a mail merge that will produce a single page for each employee and show only their entries. Is there an easy way to do this? I have come up with a few complex work-arounds, but I'm hoping there's a simpler solution. I feel like it has something to do with a Directory merge, but I can't figure out how that works and the few resources I found in a google search didn't seem to match.

In case my description isn't clear, I'll attach a sample of the spreadsheet and of the desired output.

Source file

Desired output
posted by robverb to Computers & Internet (4 answers total) 2 users marked this as a favorite
Pivot table formatted to page break between top level rows.
posted by bfranklin at 8:39 AM on April 7, 2020

Yeah, pivot table. Pretty easy.
posted by notsnot at 8:41 AM on April 7, 2020

Response by poster: Thanks for those answers. Even though I'm pretty good with Excel, I've never had the opportunity/need for pivot tables. The quick videos I just watched didn't really tell me how to get to what I need - although I'm sure if I stick with it I'll get there.

Unfortunately, even though there's a long-term benefit in investing the time in getting to really understand PTs enough that I can use them for my purposes, it might be quicker for now to just use my cumbersome work-arounds, unless anyone can point me to directions that tell me exactly how to do what I need.
posted by robverb at 9:29 AM on April 7, 2020

This should work fine in a mail merge (Microsoft Word). I'm not sure how familiar you are with a Word mail merge. Set this up as a 'letter' in word. Type in the things you want on each page, then insert the fields from Excel.

In Excel, make sure your data is sorted how you want it to appear. Then duplicate your 'employee name field'. In the duplicated column, go through and delete all but the first time each name appears.

You'll need to go through the resulting Word file and start a new page when you see a new employee's name, but I'm pretty sure this will work.
posted by hydra77 at 11:21 AM on April 7, 2020 [1 favorite]

« Older Find this weird, magical, deeply strange book I...   |   We have a few days to prepare - what to do? Newer »
This thread is closed to new comments.