Excel as a Database for MailMerge -- How to Produce a List with Variable Results

[lindan8r]

I am using MSExcel as a database to produce a document in MSWord via MailMerge.

The database needs to be able to create a list of signatories that will vary depending on the document. For example, Mary, Joe, and Laura might need to sign Documents 1, 2, and 3, but only Mary and Joe will sign Documents 1 and 4.

Here is a sample of what I need the final data to look like:

Signatory............Doc#...............Title..................Date
Mary....................1................Guidance.............8/1/13
Mary....................2................Operations...........1/1/13
Mary....................3................Tests..................5/5/13
Mary....................4................Hazards...............6/7/13
Joe......................1................Guidance.............8/1/13
Joe......................2................Operations...........1/1/13
Joe......................3................Tests..................5/5/13
Joe......................4................Hazards...............6/7/13
Laura...................2................Operations...........1/1/13
Laura...................3................Tests..................5/5/13


The problem I'm having is that there are about 45 employees, who will sign up to a total of 72 documents, so the list will be huge, and sometimes we add documents to the list. Therefore, I'd prefer not to have to hand-type each person's name and document information in each time they sign (every quarter).

I've already created a worksheet that will filter the names (via macro) based on the documents that they should sign. But...I don't know how to make that list into the format above, since the names have to repeat for each document, and since the number of names and documents will vary.

This is a fairly complicated thing, so I'd be happy to post a sample worksheet, if that's allowed and would help. I have no clue how to code in VB, but am willing to use a code that someone provides.

Thanks in advance for any help that you can offer. Please let me know if you need more details.

 

[lindan8r]

UPDATE: Here's a better "picture" of my data.

Signatory-----SOP#-------- SOP_Title--------- SOP_Approval------Name1----Name2------Name3
________----M4001---------Guidance-----------2/1/2012---------Linda------Troy-------Donnie
________----M4021---------Operations---------3/1/2012---------Linda------Troy-------Donnie
________----M4065---------Tests--------------4/1/2012---------Linda------Troy-------Donnie
________----M4107---------Hazards------------5/1/2012---------Linda------Troy-------Donnie


I'm getting closer.

I found the following VB script that will put in one name automatically:

Sub Do_Until()
x = 2
y = 5
Do Until Cells(x, 2) = ""
Cells(x, 1) = Cells(x, 5)
x = x + 1
y = y + 1
Loop
End Sub

This script will put the name "Linda" in all cells in Col A until Col B is blank. What I need it to do is to fill in the first name, then repeat all of the data (Cols B thru D) with the next name until the names run out. The final result should look something like this:

Signatory-----SOP#-------- SOP_Title--------- SOP_Approval
Linda--------M4001---------Guidance-----------2/1/2012
Linda--------M4021---------Operations---------3/1/2012
Linda--------M4065---------Tests--------------4/1/2012
Linda--------M4107---------Hazards------------5/1/2012
Troy--------M4001---------Guidance-----------2/1/2012
Troy--------M4021---------Operations---------3/1/2012
Troy--------M4065---------Tests--------------4/1/2012
Troy--------M4107---------Hazards------------5/1/2012
Donnie------M4001---------Guidance-----------2/1/2012
Donnie------M4021---------Operations---------3/1/2012
Donnie------M4065---------Tests--------------4/1/2012
Donnie------M4107---------Hazards------------5/1/2012

Hopefully that makes things clearer. Thanks!