Nested If then else Mail Merge

ac66bronco

New Member
Joined
Dec 10, 2014
Messages
44
Hey all need some probably simple help.

I need to do a mail merge with 3 conditions.

The excel file cell will have either B1 B2 or Both entered

If B1 I want the out put to be

Two Years most recent Federal Tax Returns and all supporting W-2s Tom Jones

If B2 I want the out put to be

Two Years most recent Federal Tax Returns and all supporting W-2s Brenda Jones

If Both I would obviously like

Two Years most recent Federal Tax Returns and all supporting W-2s Tom Jones & Brenda Jones

This is what I have for both but I am probably way off from a solution.

{ IF { MERGEFIELD Two_Years_most_recent_Federal_Tax_Return = "Both" "Two
Years most recent Federal Tax Returns and all supporting W-2s" ""}
{ IF { MERGEFIELD BrnApp_} = "Both" "Two Years most recent Federal Tax Returns and all
supporting W-2s" { MERGEFIELD "Borrower Name" }{ MERGEFIELD "Borrower Name 2"}
""}

As always any help is greatly appreciated.
 
Assuming your data fields are blank if there's no output, you could use a field coded like:
{IF«Two_Years_most_recent_Federal_Tax_Return»<> "" "Two Years most recent Federal Tax Returns and all supporting W-2s for «Borrower_Name»{IF«BrnApp»<> "" " and «Borrower_Name_2»}¶
" {IF«BrnApp»<> "" "Two Years most recent Federal Tax Returns and all supporting W-2s for «Borrower_Name_2»¶
"}}

Simply add the field code to an already-bulleted paragraph, then delete the trailing paragraph break when you're done.

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required. Instead of the ¶ symbols you should use real paragraph breaks.

For a macro to convert the above field code representation into a working field code, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks thread at: Mailmerge Tips & Tricks
 
Upvote 0
I understand a little less than half of what you said.

Understood about the F9 and semi understand about the chevrons (i.e. '« »')

Pretty sure you want me to select everything between them ie «Two_Years_most_recent_Federal_Tax_Return» and while selected select insert merge field from the tool bar and select the field.

same with «Borrower_Name» and «Borrower_Name_2»

Am I correct?

What about

«BrnApp»<>?

Sorry I am a noob.
 
Upvote 0
You would create the
«Two_Years_most_recent_Federal_Tax_Return»
«Borrower_Name»
«BrnApp»
«Borrower_Name_2»
fields via the Insert mergefield dropdown.

Or you could just insert the text as posted, then select it and run the conversion macro I pointed you to in the link.
 
Upvote 0
Ok I think I am getting closer. I am not sure how «BrnApp» got into my original coding but it shouldn't be there.

{ IF "{ MERGEFIELD Two_Years_most_recent_Federal_Tax_Return }" = "Both" "Two Years most recent Federal Tax Returns and all supporting W-2s { MERGEFIELD Borrower_Name } & { MERGEFIELD Borrower_Name_2 }"
{ IF "{ MERGEFIELD Two_Years_most_recent_Federal_Tax_Return }" = "B1" "Two Years most recent Federal Tax Returns and all supporting W-2s { MERGEFIELD Borrower_Name }"
{ IF "{ MERGEFIELD Two_Years_most_recent_Federal_Tax_Return }" = "B2" "Two Years most recent Federal Tax Returns and all supporting W-2s { MERGEFIELD Borrower_Name_2 }"

"" } }
 
Upvote 0
crap. sorry starting off in a really bad day. but the above is a very bare bones of what im trying to accomplish.

work wont let me dl xl2bb
 
Upvote 0
In that case, all you need is 4 separate fields without the need for any nesting:
{IF«Two_Years_most_recent_Federal_Tax_Return»<> "" "Two Years most recent Federal Tax Returns and all supporting W-2s"}
{IF«Two_Years_most_recent_Federal_Tax_Return»= "Both" "«Borrower_Name» & «Borrower_Name_2»"}
{IF«Two_Years_most_recent_Federal_Tax_Return»= "B1" "«Borrower_Name»"}
{IF«Two_Years_most_recent_Federal_Tax_Return»= "B2" "«Borrower_Name_2»"}
all input on the same line.

Indeed, if «Two_Years_most_recent_Federal_Tax_Return» is never empty, you can insert 'Two Years most recent Federal Tax Returns and all supporting W-2s' before just 3 separate fields:
{IF«Two_Years_most_recent_Federal_Tax_Return»= "Both" "«Borrower_Name» & «Borrower_Name_2»"}
{IF«Two_Years_most_recent_Federal_Tax_Return»= "B1" "«Borrower_Name»"}
{IF«Two_Years_most_recent_Federal_Tax_Return»= "B2" "«Borrower_Name_2»"}
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top