Nextif Function and Mail Merge

[fabiodero79]

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

[#2]

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

[#3]

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.

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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?

[#9]

@chrispughcmcss 

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

[#10]

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

 Thanks for replying back so quickly. I appreciate your help. 

[#11]

@chrispughcmcss 

I'm not sure how you are familiar with Power Query. Here we query the source, Group By names, aggregate other columns using concatenation of values for each name with line break in between, return result into Excel sheet. To make line breaks visible we shall apply Wrap text in formatting of these cells.

[#12]

@Sergei Baklan I am trying to solve the same question Chris had. I am not very familiar with Power Query. I have been able to Group By the necessary column but I can't find an option to aggregate via concat the other columns to later wrap. I've tried clicking on advanced settings but I only see options to sum, count, etc. Thank you for your time. 

[#13]

@DataMatt 

You may start from any function in user interface, e.g. from SUM, and after that change it in formula bar on required. For example, if List.Sum([A]) is generated, it could be changed on Text.Combine([A], ",")

[#14]

@@Sergei Baklan thank you so much! Used your suggestion and accomplished what I needed to. 

[#15]

@fabiodero79 

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.