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
shogun5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you could create the mail merge off the original School sheet. If I were to do it, I would

- create shorter column titles for columns C to CF, for example ColumnC, ColumnD, ColumnE etc.
- use { IF } fields in the Mail merge document to determine whether or not to include the column.

Something like

{IF { MERGEFIELD ColumnC } = "TRUE" "" "Block center: Wood blocks about 390¶
"}{IF { MERGEFIELD ColumnD } = "TRUE" "" "Block center: Traffic signs¶
"}{IF { MERGEFIELD ColumnE } = "TRUE" "" "Block center: Wooden or plastic figures¶
"}
etc.

Note that the { and } signs are not typed. You need to hit Ctrl-F9 to enter a field and then type between the { } signs. That also goes for inserting a { MERGEFIELD something}

The paragraph mark means that you hit the enter key at that point in the line.

If you do this in Word, you have much more control over what will be displaying. If a field value is = TRUE, Word will not print anything and move on to the next field. If it contains "FALSE" Word will print the text specified in the next text string, followed by a paragraph mark.

cheers, teylyn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shogun5Author Commented:
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 }
shogun5Author Commented:
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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

shogun5Author Commented:
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)Microsoft MVP ExcelCommented:
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
shogun5Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.