Mail merge with looping/grouping-CodePudding

I have an Excel table, which I use to do a mail merge into word.

The mail merge is done through this code

'starting the mail merge for the main body of the report
Set wdDoc = wdApp.Documents.Open(fNameW)
wdDoc.Activate
wdDoc.MailMerge.OpenDataSource Name:=(fNameE), Revert:=False, Connection:="Entire Spreadsheet", SQLStatement:="SELECT * FROM `'Table of Recommendations$'`", SQLStatement1:=""
With wdDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With
    .Execute
    
    For Each wd In ActiveDocument.StoryRanges
    With wd.Find
        .Text = "(blank)"
        .Replacement.Text = ""
        .Forward = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
    
    With wd.Find
        .Text = "^b"
        .Wrap = wdFindContinue
        While .Execute
            wd.Delete
            wd.InsertParagraph
        Wend
    End With
    Next wd

And this is the output I get:

Now, my question. What I would like to achieve is that recommendation number (b) gets inserted in the first table, just under recommendation number (a), based on the fact that the two recommendations arise from the same issue Country Cooperation. In other words, the merge process should loop through the Excel table and if the issue is the same, it should group the recommendations together, omit the blank cells, and not generate the second table. Do you think this is possible? If yes, can you point me in the right direction? I have searched allover the internet but have not been able to find any solution. Thank you.

CodePudding user response：

I do propose that you change your data! Include e.g. "RecommendationText" values which you have in two cells now into one multiline cell:

(a) Expedite an evaluation ... [AltGr][Enter]
(b) Develop, publish and disseminate

CodePudding user response：

add a concatenating column to a copy of your database data
=IF($C13="",E12&CHAR(10)&D13,E12) ... for the row 12 in the example below
filter your data for non empty lines (e.g. col "Background")

.
.
Screenshot with "concatenating column"

.
.
Screenshot filtered data