Nextif Function and Mail Merge

%3CLINGO-SUB%20id%3D%22lingo-sub-359523%22%20slang%3D%22en-US%22%3ENextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359523%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20a%20mail%20merge%20where%20I%20want%20separated%20email%20to%20be%20sent%20to%20a%20list%20of%20accounts.%3C%2FP%3E%3CP%3EI%20would%20like%20James%20Green%20to%20receive%20one%20email%20only%20stating%3A%3C%2FP%3E%3CP%3E21%2F11%2F2018%26nbsp%3B%20%26nbsp%3B%2089%3C%2FP%3E%3CP%3E26%2F12%2F2018%26nbsp%3B%20%26nbsp%3B%2034%3C%2FP%3E%3CP%3EAnd%20same%20for%20Thomas%20Reed%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EAmount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJames%20Green%3C%2FTD%3E%3CTD%3E21%2F11%2F2018%3C%2FTD%3E%3CTD%3E89%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJames%20Green%3C%2FTD%3E%3CTD%3E26%2F12%2F2018%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Reed%3C%2FTD%3E%3CTD%3E14%2F12%2F2016%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Reed%3C%2FTD%3E%3CTD%3E27%2F05%2F2018%3C%2FTD%3E%3CTD%3E88%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20the%20perfect%20scenario%20would%20be%20to%20also%20have%20the%20subtotals%2C%20but%20this%20is%20not%20extremely%20important%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EAmount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJames%20Green%3C%2FTD%3E%3CTD%3E21%2F11%2F2018%3C%2FTD%3E%3CTD%3E89%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJames%20Green%3C%2FTD%3E%3CTD%3E26%2F12%2F2018%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJames%20Green%20Total%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E123%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Reed%3C%2FTD%3E%3CTD%3E14%2F12%2F2016%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Reed%3C%2FTD%3E%3CTD%3E27%2F05%2F2018%3C%2FTD%3E%3CTD%3E88%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Reed%20Total%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGrand%20Total%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E233%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20in%20many%20different%20ways%20to%20use%20the%20formula%20%22nextif%22%20but%20I%20still%20cannot%20work%20it%20out.%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestion%2Ftutorial%20to%20share%3F%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3EFabio%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-359523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359541%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359541%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Fabio%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20if%20%7BNEXTIF%7D%20field%20helps%20for%20the%20grouping%2C%20but%20that's%20more%20question%20to%20Word%20people.%20From%20poor%20Excel%20point%20of%20view%20you%20may%20transform%20your%20source%20table%20and%20make%20grouping%20here%2C%20for%20example%20with%20Power%20Query%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20319px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84928i17852322B32C516E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESimple%20sample%20file%20is%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359539%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359539%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Eva%2C%20yes%20I%20am%20trying%20to%20do%20the%20mail%20merge%20from%20Word%20and%20the%20function%20is%20under%20%22mailings%22%20and%20%22rules%22%20but%20I%20cannot%20make%20it%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359537%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359537%22%20slang%3D%22en-US%22%3EHi!%20Usually%20MS%20Word%20is%20the%20tool%20for%20Mail%20merge.%20The%20source%20for%20it%20can%20be%20your%20excel%20sheet%20and%20table%20data.%20Sums%20are%20calculated%20in%20excel.%20But%20mail%20merge%20should%20deliver%20those%20excel%20results%20or%20adress%20data%20etc.%20where%E2%80%99s%20a%20nextif%20function%20in%20excel%3F%20It%E2%80%99s%20in%20Mail%20merge%20of%20Word%20I%20am%20sure%20with%2099%2C99%25.%20Try%20it%20with%20Word.%20Greets%2C%20Eva%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175326%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F294388%22%20target%3D%22_blank%22%3E%40fabiodero79%3C%2FA%3E%26nbsp%3Bwere%20you%20ever%20able%20to%20get%20this%20to%20work%3F%26nbsp%3B%20Is%20it%20possible%20the%20NEXTIF%20logic%20has%20morphed%20into%20something%20else%20in%20a%20newer%20release%20of%20Excel%3F%26nbsp%3B%20I'm%20stuck%20on%20the%20same%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1341289%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1341289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F558384%22%20target%3D%22_blank%22%3E%40MarthaN%3C%2FA%3E%26nbsp%3BI'm%20working%20on%20this%20right%20now%20for%20my%20own%20project%20(with%20a%20bunch%20of%20tables%20I%20want%20to%20populate%2C%20which%20seems%20to%20be%20a%20bigger%20hassle%2C)%20and%20NEXTIF%20progresses%20my%20mail%20merge%20just%20fine.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20getting%20the%20mail%20to%20only%20display%20information%20that%20is%20new%20involves%20more%20steps%2C%20namely%20placing%20that%20information%20inside%20of%20an%20if%20statement.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20subsequent%20lines%20should%20look%20something%20like%20%7B%20NEXTIF%20condition1%20%3D%20condition2%20%7D%20%7BIf%26nbsp%3Bcondition1%20%3D%20condition2%20%7BMERGEFIELD%20%22Item%22%7D%20%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20it%20easiest%20to%20add%20columns%20to%20excel%20for%20my%20conditions%20(true%2Ffalse)-%3C%2FP%3E%3CP%3Eand%20if%20so%20desired%2C%20a%20column%20to%20COUNT%20how%20many%20times%20a%20customer%20appears%20on%20the%20list%2C%20which%20only%20appears%20on%20the%20first%20line%20of%20mail%20merge%20for%20that%20page.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1341303%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1341303%22%20slang%3D%22en-US%22%3EOh%2C%20I%20misread%20the%20subtotals%20idea.%20As%20presented%20in%20the%20original%20post%2C%20you%20would%20need%20to%20make%20a%20string%20out%20of%20the%20name%20%2B%20%22%20total%22%20and%20use%20that%20as%20a%20condition%20in%20the%20final%20line%20on%20a%20page%20(or%20organize%20your%20original%20table%20so%20that%20the%20subtotal%20appears%20on%20the%20first%20line%20instead%20of%20the%20last%2C%20or%20in%20another%20column%2C%20etc.)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2244453%22%20slang%3D%22en-US%22%3ERe%3A%20Nextif%20Function%20and%20Mail%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2244453%22%20slang%3D%22en-US%22%3EThe%20bottom%20example%20from%20the%20screenshot%20is%20EXACTLY%20what%20I%20need%20to%20help%20with%20an%20email%20mail%20merge%20from%20Word.%20We%20have%20presenters%20that%20have%20multiple%20sessions.%20We%20are%20trying%20to%20send%20only%201%20email%20per%20presenter.%20After%20following%20your%20suggestion%2C%20I%20have%20been%20able%20to%20to%20get%20Power%20Query%20to%20bring%20all%20the%20sessions%20into%201%20cell%2C%20but%20am%20not%20sure%20how%20to%20make%20each%20show%20on%20its%20own%20line%20within%20the%20same%20cell.%20Can%20you%20share%20that%2C%20please%3F%3C%2FLINGO-BODY%3E
New Contributor

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. 

 

NameDateAmount
James Green21/11/201889
James Green26/12/201834
Thomas Reed14/12/201622
Thomas Reed27/05/201888

 

Then the perfect scenario would be to also have the subtotals, but this is not extremely important:

NameDateAmount
James Green21/11/201889
James Green26/12/201834
James Green Total 123
Thomas Reed14/12/201622
Thomas Reed27/05/201888
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
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.

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

image.png

Simple sample file is attached

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

@chrispughcmcss 

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

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

 

image.png

 

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. 

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