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



## doucecool57 (May 30, 2019)

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...



<colgroup><col width="160" span="7" style="width:120pt"></colgroup><tbody></tbody>



<colgroup><col><col><col><col><col><col><col></colgroup><tbody>

</tbody> 
PrefixFirst NameLast NameAddressCityStateZipMrTomBrady6 Patriots RoadFoxboroMA02035Mrs.GiseleBundchen6 Patriots RoadFoxboroMA02035

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>

</tbody>

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


Thanks!


----------



## Macropod (May 30, 2019)

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 (May 31, 2019)

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 (May 31, 2019)

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 (May 31, 2019)

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 (Jul 9, 2019)

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


----------

