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,786
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,786
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,786
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

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 10:59 AM.


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