Excel/Word Mail Merge - Same Address...different names

[doucecool57]

Not sure if more of an excel question or a Word Question but here is my issue. We use an EHR where the contact report that is exported has every contact separated into their own row in excel...

for example: mom and dad, living in the same house, have separate entries in the form and are on their own rows within the excel report. I would like to combine mom and dad on one address block for an envelope so they do not receive two invitations to the same event...
[TABLE="width: 1120"]
<colgroup><col width="160" span="7" style="width:120pt"></colgroup><tbody></tbody>[/TABLE]

[TABLE="width: 492"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 492"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Prefix[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]Mr[/TD]
[TD]Tom[/TD]
[TD]Brady[/TD]
[TD]6 Patriots Road[/TD]
[TD]Foxboro[/TD]
[TD]MA[/TD]
[TD="align: right"]02035[/TD]
[/TR]
[TR]
[TD]Mrs.[/TD]
[TD]Gisele[/TD]
[TD]Bundchen[/TD]
[TD]6 Patriots Road[/TD]
[TD]Foxboro[/TD]
[TD]MA[/TD]
[TD="align: right"]02035[/TD]
[/TR]
</tbody>[/TABLE]


Desired Result:
Mr. Tom Brady and Mrs. Gisele Bundchen
6 Patriots Road
Foxboro, MA 02035


Thanks!

 

[Macropod]

To do this effectively, you'll need to restructure your data so that all residents at a given address are on the same row. For example:
Prefix1 FirstName1 LastName1 Prefix2 FirstName2 LastName2 Prefix3 FirstName3 LastName3 Address City State Zip
then use field coding along the lines of that described under Conditionally Merge Spouse Data in the Mailmerge Tips and Tricks thread at: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

 

[doucecool57]

Thanks for the reply, that would be OK if I were doing everything but I am trying to create something for many people in my agency to use....will get a lot of backlash if they have to transform any data. Hoping there was an easier way to make it happen, any other thoughts?

 

[Macropod]

The transformation could be automated via an Excel macro - perhaps sending the output to a sheet specifically created for the mailmerge, so the original data can remain undisturbed. The macro that does that could even automate the mailmerge. That way, your co-workers' lives are actually simplified.

 

[Macropod]

Cross-posted at: http://www.msofficeforums.com/mail-merge/42628-excel-word-mail-merge-same-address-different.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[RainyA]

I need to do a similar thing to this person....is there anyone out there than can explain how to create the automated process via an Excel macro so that the original data remains undisturbed? I have 3 spreadsheets and the first has over 11,000 lines, so I'd really like to not have to manually alter it! Thanks in advance