Forum Discussion
Nextif Function and Mail Merge
Hello,
I am trying to get a mail merge where I want separated email to be sent to a list of accounts.
I would like James Green to receive one email only stating:
21/11/2018 89
26/12/2018 34
And same for Thomas Reed and so on.
| Name | Date | Amount |
| James Green | 21/11/2018 | 89 |
| James Green | 26/12/2018 | 34 |
| Thomas Reed | 14/12/2016 | 22 |
| Thomas Reed | 27/05/2018 | 88 |
Then the perfect scenario would be to also have the subtotals, but this is not extremely important:
| Name | Date | Amount |
| James Green | 21/11/2018 | 89 |
| James Green | 26/12/2018 | 34 |
| James Green Total | 123 | |
| Thomas Reed | 14/12/2016 | 22 |
| Thomas Reed | 27/05/2018 | 88 |
| Thomas Reed Total | 110 | |
| Grand Total | 233 |
I have tried in many different ways to use the formula "nextif" but I still cannot work it out.
Do you have any suggestion/tutorial to share?
Many thanks,
Fabio
14 Replies
ive been working on this for couple of days, it seems that MS word is not quite prepared for our particular need.
but good news is that i find a way.
you have to add a new column in your source, indicating the times of a single person appears. LIKE:
Name Date Amount times James Green 21/11/2018 89 2 James Green 26/12/2018 34 2 Thomas Reed 14/12/2016 22 3 Thomas Reed 27/05/2018 88 3 Thomas Reed 14/12/2016 22 3 Thomas Reed 29/05/2018 16 3 NOW you add a table to your base document , and insert these mergefields to the table
FIRST LINE: {MERGEFIELD Date}
SECOND LINE : {NEXTIF {MERGEFIELD times} >= 2 } {IF {MERGEFIELD times} >= 2 {MERGEFILELD Date}}
THIRD LINE : {NEXTIF {MERGEFIELD times} >= 3 } {IF {MERGEFIELD times} >= 3 {MERGEFILELD Date}}
....
XX th LINE : {NEXTIF {MERGEFIELD times} >= XX } {IF {MERGEFIELD times} >= XX {MERGEFILELD Date}}
MERGE , HOPE IT WORKS.
fabiodero79 were you ever able to get this to work? Is it possible the NEXTIF logic has morphed into something else in a newer release of Excel? I'm stuck on the same problem.
MarthaN I'm working on this right now for my own project (with a bunch of tables I want to populate, which seems to be a bigger hassle,) and NEXTIF progresses my mail merge just fine.
Actually getting the mail to only display information that is new involves more steps, namely placing that information inside of an if statement.
Your subsequent lines should look something like { NEXTIF condition1 = condition2 } {If condition1 = condition2 {MERGEFIELD "Item"} }
I found it easiest to add columns to excel for my conditions (true/false)-
and if so desired, a column to COUNT how many times a customer appears on the list, which only appears on the first line of mail merge for that page.
- Oh, I misread the subtotals idea. As presented in the original post, you would need to make a string out of the name + " total" and use that as a condition in the final line on a page (or organize your original table so that the subtotal appears on the first line instead of the last, or in another column, etc.)
Hi Fabio,
Not sure if {NEXTIF} field helps for the grouping, but that's more question to Word people. From poor Excel point of view you may transform your source table and make grouping here, for example with Power Query
Simple sample file is attached
- The bottom example from the screenshot is EXACTLY what I need to help with an email mail merge from Word. We have presenters that have multiple sessions. We are trying to send only 1 email per presenter. After following your suggestion, I have been able to to get Power Query to bring all the sessions into 1 cell, but am not sure how to make each show on its own line within the same cell. Can you share that, please?
Sorry, not sure I understood. Perhaps you may provide small sample file with manually added desired result.
- Hi! Usually MS Word is the tool for Mail merge. The source for it can be your excel sheet and table data. Sums are calculated in excel. But mail merge should deliver those excel results or adress data etc. where’s a nextif function in excel? It’s in Mail merge of Word I am sure with 99,99%. Try it with Word. Greets, Eva
Hi Eva, yes I am trying to do the mail merge from Word and the function is under "mailings" and "rules" but I cannot make it work.
