Nested If then else Mail Merge

[ac66bronco]

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.

 

[ac66bronco]

Id also like to add bullet points as the list progresses.

 

[Macropod]

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

 

[ac66bronco]

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.

 

[Macropod]

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.

 

[ac66bronco]

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 }"

"" } }

 

[ac66bronco]

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

 

[Macropod]

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»"}

 

[ac66bronco]

The field can be empty so I feel your 1st solution in your last post is best.

Also if the cell is indeed blank I don't want any mention of the two years so i suspect the first if statement can be removed entirely.

I am really having a hard time getting it to produce any results though.

I hit ctrl f9 and paste this all on one line between them.

{{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»"}}

Do I select for example «Two_Years_most_recent_Federal_Tax_Return» as what to replace with the insert merge field drop down?
or Two_Years_most_recent_Federal_Tax_Return and leave the cheverons «»

 

[ac66bronco]

https://docs.google.com/document/d/19VN1yak6qF3ydEjdeHZWelv3e1sIW7qthbmP0wIfnL0/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1g-0UXstdPMfGYJ9bdxhcreHlNpcNmjvlva6SuwIVr2I/edit?usp=sharing

Maybe this will help? not sure how it translates back and forth.