EITECHNICAL
asked on
MAIL MERGE
Hello
I have a MAIL MERGE PROBLEM. I have multiple details I want to merge with single company names. For example.
ABC LIMITED - SOMETHING HERE, SOMETHING THERE, ANOTHER THING HERE
- SOMETHING HERE1, SOMETHING THERE1, ANOTHER THING HERE1
- SOMETHING HERE2, SOMETHING THERE2, ANOTHER THING HERE2
- SOMETHING HERE3, SOMETHING THERE3, ANOTHER THING HERE3
I only want to show the data on Company name once off and the associated data with that company name as per example.
sample.xlsx
I have a MAIL MERGE PROBLEM. I have multiple details I want to merge with single company names. For example.
ABC LIMITED - SOMETHING HERE, SOMETHING THERE, ANOTHER THING HERE
- SOMETHING HERE1, SOMETHING THERE1, ANOTHER THING HERE1
- SOMETHING HERE2, SOMETHING THERE2, ANOTHER THING HERE2
- SOMETHING HERE3, SOMETHING THERE3, ANOTHER THING HERE3
I only want to show the data on Company name once off and the associated data with that company name as per example.
sample.xlsx
but where is the other details are coming from? i mean are they in separate workbooks or separate sheets?
ASKER
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.
ABCD-COMPANY-mail-merge-sample-output.do
sample.xlsx
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.
ABCD-COMPANY-mail-merge-sample-output.do
sample.xlsx
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.
Thanks
Rob H
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.
Thanks
Rob H
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.
=IF($A3<>"",B2,B2&", "&CHAR(10)&F3)
Still enter in F2 and copy right 3 columns and down as required.
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.
=IF($A3<>""," - "&B2," - " &B2&", "&CHAR(10)&F3)
When formatting in Excel, enable Wrap Text in the cell to see proper result.
ASKER
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?
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?
are you trying to create similar data highlighted in word for each of the companies?
ASKER
Hi, that's correct... so the source data is the excel file... each word document must contain the data from the source file
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.
Thanks
Rob H
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.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GMAYOR.COM solved it for me :-) thanks Many to one