Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-14-2014, 08:59 AM
bill3kradio bill3kradio is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 5
bill3kradio is on a distinguished road
Default using if and compare with multiple criteria and ranges of values


Help!

I am working on a document that needs to fill in text that is dependent on amount ranges in a single mergefield. I have three ranges of values and text associated with each.

The ranges are
0 - 417000
417001 - 650000
650001 and higher

I can get two of the three to work but not all three at once. Here's what I have:

{ If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } < 417001 } ) } = 1 "text1" "" }

{ If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 417000 < 650001 } ) } = 1 "text2" "" }

{ If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 650001 } ) } = 1 "text3" "" }

I have tried multiple iterations of the < and > but nothing seems to work.

Any suggestions? My head is starting to hurt from pounding it against this brick wall of a problem.
Reply With Quote
  #2  
Old 11-14-2014, 12:23 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,353
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

It's a whole lot simpler than that:
{IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}
I can't see where {MERGEFIELD program} has a role.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 11-14-2014, 12:37 PM
bill3kradio bill3kradio is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 5
bill3kradio is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
It's a whole lot simpler than that:
{IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}
I can't see where {MERGEFIELD program} has a role.
Thanks! I will try that.
The program field comes into play because I have several different programs that can also impact the text. 1/1 3/1 5/1 and 7/1. The program in conjunction with the amount determines the text that is used.

the group of three alternatives i posted are repeated for each of the programs.
Reply With Quote
  #4  
Old 11-14-2014, 01:42 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,353
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

In that case, you could use:
{IF{MERGEFIELD program}= "1/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "3/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "5/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "7/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 11-14-2014, 02:17 PM
bill3kradio bill3kradio is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 5
bill3kradio is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
In that case, you could use:
{IF{MERGEFIELD program}= "1/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "3/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "5/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
{IF{MERGEFIELD program}= "7/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
Thank you so much! That worked!

I've been bouncing off this problem for two days.

Most of what I know about if then in Word is self taught from what I can find on the net so this is extremely helpful.

Thanks again.
Reply With Quote
  #6  
Old 11-14-2014, 03:17 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,353
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You might also want to check out the Sticky 'Mailmerge Tips & Tricks' thread at the top of this forum: http://www.msofficeforums.com/mail-m...ps-tricks.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 11-17-2014, 10:48 AM
bill3kradio bill3kradio is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 5
bill3kradio is on a distinguished road
Default

Running into an unexpected issue with this.
The solution works perfectly, except....

For each of the types I have, 1/2 3/1 and so on, they can be either plain 2/1 or 1/1 special.

I need to be able to insert one group of text for the various amount ranges if it's a plain 1/1 and a different group of text baded on thevalue range if it's a 1/1 special.

I've tried using various wildcards but nothing seems to work.
Reply With Quote
  #8  
Old 11-17-2014, 01:06 PM
bill3kradio bill3kradio is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 5
bill3kradio is on a distinguished road
Default

Figured it out!

I added a third condition to the solution already provided.

{if {mergefield_program} = "1/1 ARM*" {if {mergefield_program}= "* - special"{if mergefield_amount}>650000

I then used the exact same syntax except the 2nd statement is "<> - special".

That seems to do the trick.
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
Lookup multiple values and compare different scenarios to get a specific result mws Excel 5 05-24-2014 04:52 AM
transpose values based on mulitple criteria mlttkw Excel 1 12-06-2013 02:00 AM
Compare content of two ranges INCLUDING FORMATTING delasson Word VBA 3 12-10-2012 03:54 AM
Change values in cells based on criteria SaneMan Excel Programming 2 02-02-2012 07:58 AM


All times are GMT -7. The time now is 02:28 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft