New Contributor

# 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

10 Replies

# Re: Nextif Function and Mail Merge

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

# Re: Nextif Function and Mail Merge

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.

# Re: Nextif Function and Mail Merge

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

# Re: Nextif Function and Mail Merge

@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.

# Re: Nextif Function and Mail Merge

@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.

# Re: Nextif Function and Mail Merge

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.)

# Re: Nextif Function and Mail Merge

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?

# Re: Nextif Function and Mail Merge

Sorry, not sure I understood. Perhaps you may provide small sample file with manually added desired result.

# Re: Nextif Function and Mail Merge

@Sergei Baklan Yes, in the examples you provided to the question. You shared the idea for using Power Query in Excel instead of NextIF in Word. The first image shows multiple rows for each person in the Name column with different information for Date and Amount. In the 2nd image shown after applying Power Query to the sheet, it shows 1 row per Name with multiple lines within each row for Date and Amount.

I found several great videos on merging and grouping data to the same row when they had a common name/data to group them by, but they all just extended into one long line.  Your example does not appear to use text wrap to fix this as there seems to be plenty of space in the Date column to have the 2nd date in the same line as the 1st date.  What did you do to get the 2nd line within the row?