S
Steve Wylie
A colleague of mine at work is doing a Word 2000 print
merge and really needs an "intelligent" merge that is
rather outside our scope of expertise for merging. I
was hoping that some kind soul on this newsgroup would be
able to help, or point us in the right direction.
She has an Excel spreadsheet with a thousand or so
records, each record is the banking details of an
employee.
Each record includes payroll reference, employee name,
bank branch name, bank sort code and employee's account
number.
The merge will produce bank details to go to various
banks. Typically each employee will have their bank
details, and some standard information that does not
change, on one sheet of paper, and this is sent off to
the bank. This is no problem.
However, lots of employees have the same bank sort code,
because they hold their account at the same branch as
certain other employees. If, for instance, 47 employees
have their account at the local Township Bank, the
Township Bank does not wish to receive 47 separate sheets
of paper with one employee's name on each. Therefore, we
need a way for the merge to tell when there is a section
of identical consecutive sort codes (the Excel sheet is
sorted by sort code), and where there is, to print these
all on one sheet of paper as a listing, and not to do one-
page-per-record as it would normally do.
I am aware that this sort of task has been explained in
help groups before, and indeed I have located a Microsoft
Knowledge Base article explaining something approaching
this (article number 105888), but there is a complication
and I cannot figure out how to work around it or even
whether it is possible in Word.
Here is the complication:
The way our company wants it, is that where there are 9
or less consecutive sort codes, these can be printed one
employee per page, like a normal merge. There is some
standard information on one page, and merged information
of the branch sort code, the bank's name and the branch's
name, then the employee details underneath.
However, where there are 10 or more employees, in
addition to the standard bank branch information and sort
code, etc, instead of the individual employee details
there will be a line at the bottom saying "see attached
sheet". The attached sheet then consist of the bank
details listed out for the 10+ employees concerned.
So for a single record you would have (in addition to a
lot of static details at the top):
Branch sort code: 12-34-56
Bank name: Township Bank
Branch name: Hamley Street branch
John Smith 12-34-56 555657565
But for 10 or more employee in a bunch who have the same
bank branch you would have:
Branch sort code: 12-34-56
Bank name: Township Bank
Branch name: Hamley Street branch
See list overleaf.
Then there would be a page break and you'd have:
John Smith 12-34-56 555657565
Jane Peterson 12-34-56 555870999
Michael Dee 12-34-56 555665432
Alex Michaels 12-34-56 555121345
I am hoping that the Word merge codes can provide a
solution. The merge has, as you can see, hard and fast
rules controlling which records are merged to which
format.
Could anyone who has experience of this kind
of "intelligent" merge point me in the right direction?
Or perhaps, dare I ask, with a full solution?
Thank you very much for reading this far!
Steve Wylie
merge and really needs an "intelligent" merge that is
rather outside our scope of expertise for merging. I
was hoping that some kind soul on this newsgroup would be
able to help, or point us in the right direction.
She has an Excel spreadsheet with a thousand or so
records, each record is the banking details of an
employee.
Each record includes payroll reference, employee name,
bank branch name, bank sort code and employee's account
number.
The merge will produce bank details to go to various
banks. Typically each employee will have their bank
details, and some standard information that does not
change, on one sheet of paper, and this is sent off to
the bank. This is no problem.
However, lots of employees have the same bank sort code,
because they hold their account at the same branch as
certain other employees. If, for instance, 47 employees
have their account at the local Township Bank, the
Township Bank does not wish to receive 47 separate sheets
of paper with one employee's name on each. Therefore, we
need a way for the merge to tell when there is a section
of identical consecutive sort codes (the Excel sheet is
sorted by sort code), and where there is, to print these
all on one sheet of paper as a listing, and not to do one-
page-per-record as it would normally do.
I am aware that this sort of task has been explained in
help groups before, and indeed I have located a Microsoft
Knowledge Base article explaining something approaching
this (article number 105888), but there is a complication
and I cannot figure out how to work around it or even
whether it is possible in Word.
Here is the complication:
The way our company wants it, is that where there are 9
or less consecutive sort codes, these can be printed one
employee per page, like a normal merge. There is some
standard information on one page, and merged information
of the branch sort code, the bank's name and the branch's
name, then the employee details underneath.
However, where there are 10 or more employees, in
addition to the standard bank branch information and sort
code, etc, instead of the individual employee details
there will be a line at the bottom saying "see attached
sheet". The attached sheet then consist of the bank
details listed out for the 10+ employees concerned.
So for a single record you would have (in addition to a
lot of static details at the top):
Branch sort code: 12-34-56
Bank name: Township Bank
Branch name: Hamley Street branch
John Smith 12-34-56 555657565
But for 10 or more employee in a bunch who have the same
bank branch you would have:
Branch sort code: 12-34-56
Bank name: Township Bank
Branch name: Hamley Street branch
See list overleaf.
Then there would be a page break and you'd have:
John Smith 12-34-56 555657565
Jane Peterson 12-34-56 555870999
Michael Dee 12-34-56 555665432
Alex Michaels 12-34-56 555121345
I am hoping that the Word merge codes can provide a
solution. The merge has, as you can see, hard and fast
rules controlling which records are merged to which
format.
Could anyone who has experience of this kind
of "intelligent" merge point me in the right direction?
Or perhaps, dare I ask, with a full solution?
Thank you very much for reading this far!
Steve Wylie