asked on

How to aggregate Data in Excel for MS Word Mail Merge

Dear Experts,

I am putting together a mail merge document in MS Word to grab teacher names, number of items ordered, and what those items are.

I have attached a sample spreadsheet (changed names) to give you an idea of what I am trying to do. The Mail Merge part I have down no problem. What I am trying to do is to aggregate all the items ordered by each teacher into column (D - Items Ordered) of the MasterList-Export worksheet. This way when I create my mail merge I can create a field from column D that will list all the items order by that teacher. Perhaps I could create a loop from C4 to CE4 on School worksheet and if the cell has the value "FALSE" (meaning that the teacher does not have the item and needs to order) I could add the title in Row 3 respectively to a string delimited by comma. I just don't know how to do this OR if there is a better way.

Thanks in advance for your assist.

If you have further questions or need me to explain better please let me know. See attachment.

Cheers!
example.xlsx

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Rudolph

ASKER

teylyn:

If I do this then wouldn't I have empty fields? For example:

Block center: Wood blocks about 390
Block center: Traffic signs
(empty as value was TRUE)
Block center: Legos

Additionally, I am trying to get around retyping all the header names. Since these header names are dynamically created from another worksheet I can't change the names. Rather I would prefer to simply 'print' the value in C3 rather than have to type Block Center: Wood blocks about 390. This way if the title changes in the master worksheet all the headers in other worksheets change automatically.

What I thought would work best is to concatenate the headers into one string if the cell contains a FALSE value. So something like if C4 = FALSE then STRING = STRING + (value of the corresponding column title) + "," (delimited by comma), etc. Then simply link the string to the mail merge field, i.e.
{MERGEFIELD items }

Mike Rudolph

ASKER

Okay,

I hope I am on the right track. In the attached (updated) version I am using the following concat1 function to grab all values from the School worksheet to populate the respective D column in the MasterList-Export sheet. The only thing I need help with is finding code to check and see if the value is False and if so write the column's respected title to the string and not the cell value.

So instead of True, False, True, False, False for the first row (Bill Pasley) I should get:
Block center: Traffic signs, Block center: Legos, Block center: Magna tiles

Just need some help with the if logic in this code.

Thanks!

Function Concat1(myRange As Range, Optional myDelimiter As String)
    Dim r As Range

    Application.Volatile
    For Each r In myRange
        Concat1 = Concat1 & r & myDelimiter
    Next r
    If Len(myDelimiter) > 0 Then
        Concat1 = Left(Concat1, Len(Concat1) - Len(myDelimiter))
    End If
End Function

Open in new window

example.xlsm

Mike Rudolph

ASKER

Something like:

Function Concat1(myRange As Range, Optional myDelimiter As String)
Dim r As Range

Application.Volatile
For Each r In myRange
'***********************************
'if r = "FALSE' then assign column's titile in row 3 respectively to the variable r
'*********************************
Concat1 = Concat1 & r & myDelimiter
Next r
If Len(myDelimiter) > 0 Then
Concat1 = Left(Concat1, Len(Concat1) - Len(myDelimiter))
End If
End Function

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

no, you wouldn't have empty fields. if the value of the field is TRUE then NOTHING gets printed. Only if the value is FALSE it will print the specified text and add a paragraph mark. standard procedure in mail merge. done all the time, but if you don' want to go there, that's fine. I just think it' way easier than manipulating the excel source with macros.

cheers, teylyn

Mike Rudolph

ASKER

Teylyn,

Well I was skeptical at first about use if then login in the Word document but it turns out that MS Word 2007 has a real nice feature for this. So I use the inherent dialogue box for conditionally display text based on values in the spreadsheet. This seems to take longer than simply modifying the values directly on the spreadsheet but it works!

Thanks for the tip!

Mike