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

doucecool57

New Member
Joined
Mar 20, 2013
Messages
8
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 NameAddressCityStateZip
MrTomBrady6 Patriots RoadFoxboroMA02035
Mrs.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!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,439
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

New Member
Joined
Mar 20, 2013
Messages
8
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,439
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.
 

RainyA

New Member
Joined
Jul 9, 2019
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,312
Messages
5,467,855
Members
406,558
Latest member
MattJC7

This Week's Hot Topics

Top