Unusual Excel-to-Word merge. Is this possible?

May 6, 2014 7:05 AM

Have Excel spreadsheet with a list of contacts with the name of the organization they belong to. Need a series of Word documents for each organization that includes the names and info of each contact. Read on for more detail.

Asking for my lovely wife:

We have an Excel spreadsheet listing contacts for organizations. Each contact has a row in the spreadsheet, so there are multiple lines for each organization. We are trying to find a way to create a Word document for each organization that shows its basic demographic info once, and then lists all the contacts for that organization along with some of their basic info (their role, email, etc.).

So the final result would be a page with the organization name, address, etc. at the top, followed by a list of all contacts that belong to that organization. Is there a way to do this out of Excel? The ideal solution is one that can be largely automated so that the data in the sheet can be updated and the merge re-run every month with minimal intervention from a person.

Thanks!

posted by that's candlepin to Computers & Internet (4 answers total)

When you build your Word document, use links to the Excel cells.

When you make changes within cells in the Excel spreadsheet (for example, the company changes its name, or one contact leaves and is replaced by a different contact), it will automatically make those changes in the Word document.

If you are adding cells or deleting cells (for example, adding a new contact where you previously had none, or deleting one who is not being replaced), you will need to make sure that you are making the same changes in the Word document that you make in the Excel spreadsheet.
posted by Bentobox Humperdinck at 7:24 AM on May 6, 2014

This link might have an answer to your question. I'll be watching this question, this is an interesting one.

If I were doing this, I might try to skip Word altogether and just create some linked Excel sheets that update from the master spreadsheet. VLOOKUP might be the tool to use.
posted by natteringnabob at 7:26 AM on May 6, 2014

I think this should be possible with a mail merge, either a normal one against an Excel pivot table, or a "mail merge with catalog".
posted by katrielalex at 7:32 AM on May 6, 2014 [1 favorite]

How I do this requires two steps and uses the "nextif" feature of Word mail merge:

First, sort your Excel doc in the order you want the items to be printed. Presumably all records from each company will be listed together.

1. In the Excel doc, create a new column that indicates whether the following record is a continuation of the current company (note the logic there--it is a bit tricky).

I usually use '1' and '0' - '1' means the following record is in the same company while '0' means the following record is the first record of a new company.

CODE COMPANY
1 ABC Enterprises
1 ABC Enterprises
1 ABC Enterprises
0 ABC Enterprises
1 Widgets, Inc
1 Widgets, Inc
0 Widgets, Inc
1 Beelzebub & Sons
1 Beelzebub & Sons
1 Beelzebub & Sons
1 Beelzebub & Sons
0 Beelzebub & Sons
1 Miko's Bakery
1 Miko's Bakery
1 Miko's Bakery
0 Miko's Bakery
etc.

2. Set up your Word merge doc using the 'nextif' operator based on the CODE field, something like this:

Mergefields etc for record #1 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #2 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #3 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #3 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #3 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #4 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #5 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #6 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #7 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #8 for each company
{ NEXTIF { MERGEFIELD CODE } = "1" }
Mergefields etc for record #9 for each company
etc.

You do have to copy the info for each merge field down the page. If you might have as many as 20 records on a single page then you'll have this part of the merge doc just copy/pasted 20 times. I don't know any way to automate that part of it (without a lot of trouble).

Also note that this approach can cause discrepancies (apparent duplicate pages/records) when using the mail merge preview. If you think the merge is malfunctioning when you preview, just try doing an actual mail merge and almost certainly you'll find it works correctly.
posted by flug at 8:59 AM on May 6, 2014 [2 favorites]