Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-03-2021, 10:31 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default Creating two independent NEXT IF sequences in the same document

A colleague asked my help to group multiple row data from an excel database to a word document using Mail Merge. I've managed to do so by following a youtube guide (link posed below*) that relies heavily on NEXT IF sequences. The example output of this in Preview Results 1 is:


"A - Blue
B - Green
C - Yellow"

So far so good but I need to repeat this same list at another point in the same word document with an additional column of information. I copy pasted the same script, added the new column of data to it and it's giving me the following example output in Preview Results 1:
"C - Yellow - London"

While I need it to give me
"A -Blue - Berlin
B - Green - Paris
C - Yellow - London"

It appears that when copy pasting the same script it only outputs the last item in the sequence. I've attached both the word with what I have so far and it's excel database.

Could anyone help me figure out how to separate these two next if sequences?
Thank you in advance!



*Link to youtube video I used for grouping multiple row data: Mail Merge Grouping Multiple row data- NEXTIF - YouTube
Attached Files
File Type: docx Example_Word.docx (15.7 KB, 2 views)
File Type: xlsx Example_Word.xlsx (10.8 KB, 2 views)
Reply With Quote
  #2  
Old 12-09-2021, 01:45 PM
Charles Kenyon Charles Kenyon is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,755
Charles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud of
Default

"script" implies some kind of programming like vba. Are you talking about the field coding? In either case, it would be helpful to include these in your post here, rather than expecting someone to open your files. Here is an add-in that helps convert field codes to text (and vice versa) Import and export field constructions as text.
Reply With Quote
  #3  
Old 12-14-2021, 08:49 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default

Thanks for the tip! Sorry if I came across a little presumptuous, it's the first time I post here.

I downloaded the add-in, let me try again following your indications.

I've written the following field code which groups data from multiple rows:

We have the following information on Employee { MERGEFIELD "Employee" }
{ MERGEFIELD Employee_ID } - { MERGEFIELD Description } { SET ID1"{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }
{ NEXTIF { MERGEFIELD Employee } = { MERGEFIELD Employee_Compare } }{ SET ID2 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ IF ID1 = ID2 "" "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ SET ID1 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }
{ NEXTIF { MERGEFIELD Employee } = { MERGEFIELD Employee_Compare } }{ SET ID2 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ IF ID1 = ID2 "" "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ SET ID1 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }
{ NEXTIF { MERGEFIELD Employee } = { MERGEFIELD Employee_Compare } }{ SET ID2 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ IF ID1 = ID2 "" "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ SET ID1 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }
{ NEXTIF { MERGEFIELD Employee } = { MERGEFIELD Employee_Compare } }{ SET ID2 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ IF ID1 = ID2 "" "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ SET ID1 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }
{ NEXTIF { MERGEFIELD Employee } = { MERGEFIELD Employee_Compare } }{ SET ID2 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ IF ID1 = ID2 "" "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }{ SET ID1 "{ MERGEFIELD Employee_ID } - { MERGEFIELD Description }" }


As expected it provides the following output for Preview Results 1 is:

We have the following information on Employee Claire
A - Blue
B - Green
C - Yellow


So far this is perfect. The challenge I can't seem to solve is repeating this output a second time in the same word document. Copy pasting the same field code later in the word document doesn't work. I've also tried to solve this by modifying the excel database but I can't seem to get that to work correctly either. My Target output for Preview Results 1 is as follows:

We have the following information on Employee Claire
A - Blue
B - Green
C - Yellow

Here is the same information again
A - Blue
B - Green
C - Yellow


Please let me know if you have any ideas on how I can get this to work properly
Reply With Quote
  #4  
Old 12-14-2021, 03:06 PM
macropod's Avatar
macropod macropod is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,249
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

You could do this quite easily using a pair of DATABASE fields in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at:
https://answers.microsoft.com/en-us/...1-1996c14dca5d
If you modify tour Excel workbook by adding a separate worksheet with a table with just a single instance of each of the grouping criteria (i.e. employess), a pair of DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach (using a single DATABASE field) can be found at:
https://answers.microsoft.com/en-us/...f-8642e46fa103
For some working examples, see:
https://www.msofficeforums.com/mail-...-multiple.html
https://www.msofficeforums.com/mail-...tml#post151706
Merge excel list into Word Receipt
(the second of these uses a macro to apply some additional formatting).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-15-2021, 07:54 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default

Thanks so much, Paul! Very interesting method, however I didn't manage to get it to work... I very closely followed the second link in your post using the exact same data and it just gave me an error message when trying to Preview Results.

Regardless, correct me if I'm wrong but I think I need to keep my main word document as a "Directory" mailmerge; in the same document I have a few paragraphs with blanks that need to be automatically filled using the mailmerge.

Please let me know if you happen to be aware of any other solutions that respect this "Directory" constraint I have. I would love to give them a go!
Reply With Quote
  #6  
Old 12-15-2021, 09:41 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default

If you think I may be making a mistake I would be more than glad (and especially grateful) to reach out to you on linkedin and organize a call where we can troubleshoot together. Let me know!

Thanks,
Charlie
Reply With Quote
  #7  
Old 12-15-2021, 03:08 PM
macropod's Avatar
macropod macropod is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,249
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

As I stated quite clearly, you'd use a ‘letter’ mailmerge main document. And, as demonstrated in the links, the DATABASE field approach does work when correctly implemented.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 12-16-2021, 10:52 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default

Yea don't worry, I was using a 'letter' mail merge doc this entire time, I thought it would limit the file in another way but, after playing around with it, it didn't. That wasn't my issue. The issue was that I had a very silly typo in the field code, I was able to catch today... so sorry, been working long hours and it slipped by me. Once I fixed it I was able to reproduce the example from the links you posted and it worked marvelously!

However, as soon as I applied it to my own data-set, it gave me two errors:

(1) Error has occurred: Syntax error (missing operator) in query expression '[Soggetto] = 4ward S.r.l.'.
Note: 4wared S.r.l is the first item under the subject column in my excel file.

(2) Word was unable to open the data source.

Here is my field code. I really can't see what is wrong with it... I've been trying everything:

{DATABASE \d "{FILENAME \p}/../PEC Esternalizzatori_v.Stampaunione.xlsx" \s " SELECT [Nr. Documento], [Descrizione - PEC], [Tipologia Tassonomia] FROM [PEC$] WHERE [Soggetto] = {MERGEFIELD Soggetto} " \l "15" \b "49" \h}

According to the first message, the pain-point should be in the "WHERE [Soggetto] = {MERGEFIELD Soggetto}" part. I really can't seem to find any mistakes in this field code. Let me know if you see anything! The second error message hopefully will clear up once the first one is fixed.

Thank you so much again, I cannot begin to express how educating this has been for me.
Reply With Quote
  #9  
Old 12-23-2021, 02:14 AM
bananapple bananapple is offline Creating two independent NEXT IF sequences in the same document Windows 10 Creating two independent NEXT IF sequences in the same document Office 2019
Novice
Creating two independent NEXT IF sequences in the same document
 
Join Date: Dec 2021
Posts: 6
bananapple is on a distinguished road
Default

Quick update: I was able to solve my issue!

To anyone that may be trying to apply this method, make sure your data is formatted as a string in your excel dataset (I believe that is what the "\s" represents in Paul's field code). So look out for any green triangles in your dataset that may say "convert to number" or something like that when clicked on.

After repeating this method for a number of different scenarios I also found that manually retyping the column names in my excel datasets helped even in the absence of typos.

Special thanks again to Paul and merry christmas!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove number sequences mvtpinheiro Excel 3 05-09-2014 07:12 PM
animate long image sequences validusername PowerPoint 0 12-24-2013 07:10 PM
Sequential Numbering on Tickets Multiple number Sequences skitchen Word 2 10-01-2013 06:41 AM
Looping custom animation sequences or a selection of slides regilala PowerPoint 0 02-07-2010 07:35 PM
Creating two independent NEXT IF sequences in the same document Independent columns jdthelen Word 2 09-17-2009 09:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft