Merge from multiple excel columns

[Rayashe]

Hi,
I am operating on Office 2007.
I have a word template that I need to merge to from an Excel table. The
template can show 5 payments next to each other in columns.
Problem is that some clients may have only had one payment and others can
have up to 12 payments.
What can I do so that if clients have had 5 payments or less, then it will
not print/email/merge to another 2 pages that will have no entries?
Thanks.

 

[Peter Jamieson]

Can you be a bit more specific about how the data is set up in your Excel
table? Is each payment in a separate row in the table? Or can you have up to
12 payments in the same row? If you can have up to 12 payments in the same
row, can there be gaps, like this

p1 p2 p3 p4 p5 p6 p7 p8 p9 p10 p11 p12
10 12 8 5

or what?

I have a word template that I need to merge to from an Excel table. The
template can show 5 payments next to each other in columns.

Also for this: how does your output need to look if there are 5 payements? 6
payments? 12 payments?

 

[Rayashe]

Hi Peter,
Each client has their own row which can have up to 12 payments (maybe more a
year down the line). There are no gaps between payments.

Example of payments is as follows:

Name Surname Pay1 Int1 Date1 Pay2 Int2 Date2 Pay3 Int3 Date3 Etc.
John Brown 100 10 9/8/8 200 20 15/9/8 100 10 7/10/8
Mark Black 500 50 15/9/8 300 30 20/12/8
Mary White 400 40 1/4/8 700 70 3/6/8 600 60 9/8/8
Etc..

 

[Doug Robbins - Word MVP]

How do you want the information to appear in the documents that are created
by the mail merge?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Rayashe]

Hi Doug,

It needs to go into http://www.hmrc.gov.uk/ebu/p11dws4-2008.pdf which I have
converted to word in order to do the mailmerge direct to email. As you will
see, it only allows for 5 payments to be shown.
Thanks

 

[Doug Robbins - Word MVP]

I gather that if a person has more than 5 loans, you want to complete a
second form for that person, and if more than 10, you would be completing a
third form.

I would suggest that you will need to do this in three separate merge
operations, using 3 separate data sources - one for the first (up to 5)
loans, one for the 6th up to 10th loan and one for the 11th and 12th loan.
The second and third data sources would only have records for those persons
who had the relevant number of loans.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Rayashe]

Hi Doug,
That is correct with regards to needing a 2nd or 3rd form depending on
number of loans.
I could add another column to my data source and put a count function in it
to filter the data in the 3 different merge docs.
I thought there may have been some sort of code I could add which would be
able to do this using one document. Do you think it would help to post this
query into the programming section?
Thanks.

 

[Doug Robbins - Word MVP]

If you want to know if it is possible to automate the task using VBA, then
the answer is yes. It would however no longer be done by using mailmerge.

To do it, you would use VBA to iterate through the rows in the data source,
determining just what each record contained and then it would create the
necessary documents from a template that contained docvariable fields
instead of merge fields and it would create variables in the document, the
values of which it would set to the relevant data from the record so that
the docvariable fields would display the required information.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Rayashe]

Thanks for help. May just be easier to create the different merge documents
at this stage.