I have a MAIL MERGE PROBLEM. I have multiple details I want to merge with single company names. For example.


I only want to show the data on Company name once off and the associated data with that company name as per example.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
but where is the other details are coming from?  i mean are they in separate workbooks or separate sheets?
EITECHNICALAuthor Commented:
Hi,  I have attached new sample source data with what it should look as merged output data

The problem I am having is to avoid 5000 letters to go out from 50 clients - the 50 clients must have all their associated records or details, not 1 record / detail for each line.
Rob HensonFinance AnalystCommented:
Add a row above your data for headers and then use the following formulas in row 2 down.

In column F use:
=IF($A3<>"",B2,B2&", "&F3)

In Column G use:
=IF($A3<>"",C2,C2&", "&G3)

In column H use:
=IF($A3<>"",D2,D2&", "&H3)

Enter in column A and you can copy across and then copy down for extent of data. Then use columns A, F, G & H in mail Merge with filter on column A for excluding blank.

Rob H
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Rob HensonFinance AnalystCommented:
Slight change to allow for carriage return:

=IF($A3<>"",B2,B2&", "&CHAR(10)&F3)

Still enter in F2 and copy right 3 columns and down as required.
Rob HensonFinance AnalystCommented:
Another change to allow for dash:

=IF($A3<>""," - "&B2," - " &B2&", "&CHAR(10)&F3)

When formatting in Excel, enable Wrap Text in the cell to see proper result.
EITECHNICALAuthor Commented:
Thanks Rob, I am a bit lost here... will this be for many to one relationship, in othe r words, many records for one company on one letter?
ProfessorJimJamMicrosoft Excel ExpertCommented:
if i know the question, perhaps i can answer it quickly. i still did not understand what is required. there is a word document with some data in it. and excel file with 3 company data.

are you trying to create similar data highlighted in word for each of the companies?
EITECHNICALAuthor Commented:
Hi, that's correct... so the source data is the excel file... each word document must contain the data from the source file
Rob HensonFinance AnalystCommented:
EITECHNICAL - not sure I understand your question about One to Many Relationship.

By using the formulae I provided, the relevant data will be combined from multiple rows of separate data into one row of concatenated data. If you then use a filter on the merge excluding where column A is blank, you will only use the rows that have company name populated and the combined data against each. You should then only have one record per company and that should produce only one letter per company.

Rob H
ProfessorJimJamMicrosoft Excel ExpertCommented:
I understand now,  the data you have is in a strange way,

there are many ways to do this,   VBA, non-VBA  Add-ins

first this Article  it the toturial similar to your situation, all you need to do is to transpose your data either with copy and paste transpose or  the transpose array formula.
see if this works for you.

another option is using free Add-in of Many to one  it has a well detailed explanation.

there is also another add-in Mailmerge tool 2015  that is also handy in many cases.

also there is a very good example of CatalogueMailmerge

see which one works for you.  and if all options fail, which i doubt if it would, then i would perhaps write a for a customized VBA solution for  you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EITECHNICALAuthor Commented:
GMAYOR.COM solved it for me :-) thanks Many to one
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.