Forum Discussion
Mail merge nested if statements
Mailmerge challenge
This application generates a single 3 x 4 label for each wishlist gift from these disadvantaged kids. The stickers will be taken by individuals who'll get the gift to be presented to the kid
landingsholidaycharitablefoundation.org
Thanks for your help with this nesting issue....
Database in addition to demographics, contains 6 fields called wish list 1,2,3 etc
I want to print labels that include some demographic stuff, like name.
Then need to print a single wishlist item onto a label.
Loop to the next wishlist item if not empty.
If not empty, go to next wishlist item
Continue to loop through all 6 wishlists for this kid
If a wishlist item is empty, then advance to the next record and repeat.
Database:
First Name,Family #,Source,Age,Gender,Wish List 1,Wish List 2,Wish List 3,Wish List 4,Wish List 5,Wish List 6 | ||||||
Jaxson W.,7,BLUE BUTTERFLY,8,Male,Hexbugs,Legos,,,, |
| |||||
Odette,8,BLUE BUTTERFLY,9,Female,Mote Marina Tickets,Amazon gift card,Hatchimals,,, |
| |||||
Amelie,8,BLUE BUTTERFLY,12,Female,Amazon gift card,Mini golf gift card,The Hobbit series,,, |
| |||||
Aaliyah H.,9,BLUE BUTTERFLY,8,Female,New shoes (skating shoes),Magna books (anime),Black hoodie,,, |
| |||||
Haley W.,10,BLUE BUTTERFLY,11,Female,Anything with The Beatles,Roblox gift card,Drawing supplies,,, |
| |||||
Michael G.,11,BLUE BUTTERFLY,5,Male,Ryan's World Shadow Warrior Mystery Box,Ryan's World Royal Treasure Chest,Just Dance Switch 2021,,, |
| |||||
Lily G.,11,BLUE BUTTERFLY,9,Female,"Harry Potter Lego--4 Privet Drive, #75968",Lego Hidden Side--#70432,Electric scooter,,, |
| |||||
Jonathan G.,11,BLUE BUTTERFLY,13,Male,Playstation Gift Card,Nike Basketball Shorts size Large,Tampa Buc's T Shirt Size Large,Nike TShirt Size Large,, |
| |||||
Summayah,12,BLUE BUTTERFLY,8,Female,Baby doll,Baby doll carrier,Barbies,,, |
| |||||
Keplar,13,BLUE BUTTERFLY,11,Male,Stickbots,Legos,The Far Side Comics,,, |
| |||||
Tasman,13,BLUE BUTTERFLY,13,Female,Loopity--loops yarn to make blankets (any color),Bath bombs/face masks (for sensitive skin),Cat necklace/earrings/bracelet,,, |
| |||||
Output : 3 x 4” label
Name
gender age family#
wishlist item
source
Use the following field construction, using CTRL+F9 to insert each pair of field delimiters { } and pressing the Enter key, or SHIFT+Enter where the ¶ appear.
«Name»
«gender» «age» «family#»
«wish_list_1»{IF «wish_list_2» <> "" "¶
«wish_list_2»" }{IF «wish_list_3» <> "" "¶
«wish_list_3»" }{IF «wish_list_4» <> "" "¶
«wish_list_4»" }{IF «wish_list_5» <> "" "¶
«wish_list_5»" }{IF «wish_list_6» <> "" "¶
«wish_list_6»" }
«source»
Deleted
Thank you so much!!!! But I'm having humility lessons . After I get my coding done, and I run a preview, all my coding EXCEPT THE FIELD NAMES DISAPPEAR.
Can u suggest what I'm doing wrong??
- Perhaps my goal was unclear. I need one wishlist per label. Thus up to 6 labels per name
In what application is the data? If you have it in an Excel Workbook and you run a macro containing the following code
Dim shtsource As Worksheet
Dim shttarget As Worksheet
Dim c As Long, r As Long, rowtarget As Long, c1 As Long
Set shtsource = Sheets(1)
Set shttarget = Sheets.Add
For c = 1 To 5
shttarget.Range("A1").Offset(0, c - 1) = shtsource.Range("A1").Offset(0, c - 1)
Next c
shttarget.Range("A1").Offset(0, 5) = "Wish Item"
rowtarget = 1
With shtsource.Range("A1")
For r = 1 To .CurrentRegion.Rows.Count - 1
For c = 1 To 6
shttarget.Range("A1").Offset(rowtarget, c - 1) = .Offset(r, c - 1)
Next c
rowtarget = rowtarget + 1
For c1 = 7 To 12
If .Offset(r, c1 - 1) <> "" Then
For c = 1 To 5
shttarget.Range("A1").Offset(rowtarget, c - 1) = .Offset(r, c - 1)
Next c
shttarget.Range("A1").Offset(rowtarget, 5) = .Offset(r, c1 - 1)
rowtarget = rowtarget + 1
End If
Next c1
Next r
End WithIt will add a sheet to the workbook with the data re-arranged as shown below which can then be used as the data source for a straight forward Label type mail merge
First Name Family # Source Age Gender Wish Item Jaxson W. 7 BLUE BUTTERFLY 8 Male Hexbugs Jaxson W. 7 BLUE BUTTERFLY 8 Male Legos Odette 8 BLUE BUTTERFLY 9 Female Mote Marina Tickets Odette 8 BLUE BUTTERFLY 9 Female Amazon gift card Odette 8 BLUE BUTTERFLY 9 Female Hatchimals Amelie 8 BLUE BUTTERFLY 12 Female Amazon gift card Amelie 8 BLUE BUTTERFLY 12 Female Mini golf gift card Amelie 8 BLUE BUTTERFLY 12 Female The Hobbit series Aaliyah H. 9 BLUE BUTTERFLY 8 Female New shoes (skating shoes) Aaliyah H. 9 BLUE BUTTERFLY 8 Female Magna books (anime) Aaliyah H. 9 BLUE BUTTERFLY 8 Female Black hoodie Haley W. 10 BLUE BUTTERFLY 11 Female Anything with The Beatles Haley W. 10 BLUE BUTTERFLY 11 Female Roblox gift card Haley W. 10 BLUE BUTTERFLY 11 Female Drawing supplies Michael G. 11 BLUE BUTTERFLY 5 Male Ryan's World Shadow Warrior Mystery Box Michael G. 11 BLUE BUTTERFLY 5 Male Ryan's World Royal Treasure Chest Michael G. 11 BLUE BUTTERFLY 5 Male Just Dance Switch 2021 Lily G. 11 BLUE BUTTERFLY 9 Female "Harry Potter Lego--4 Privet Drive Lily G. 11 BLUE BUTTERFLY 9 Female #75968" Lily G. 11 BLUE BUTTERFLY 9 Female Lego Hidden Side--#70432 Lily G. 11 BLUE BUTTERFLY 9 Female Electric scooter Jonathan G. 11 BLUE BUTTERFLY 13 Male Playstation Gift Card Jonathan G. 11 BLUE BUTTERFLY 13 Male Nike Basketball Shorts size Large Jonathan G. 11 BLUE BUTTERFLY 13 Male Tampa Buc's T Shirt Size Large Jonathan G. 11 BLUE BUTTERFLY 13 Male Nike TShirt Size Large Summayah 12 BLUE BUTTERFLY 8 Female Baby doll Summayah 12 BLUE BUTTERFLY 8 Female Baby doll carrier Summayah 12 BLUE BUTTERFLY 8 Female Barbies Keplar 13 BLUE BUTTERFLY 11 Male Stickbots Keplar 13 BLUE BUTTERFLY 11 Male Legos Keplar 13 BLUE BUTTERFLY 11 Male The Far Side Comics Tasman 13 BLUE BUTTERFLY 13 Female Loopity--loops yarn to make blankets (any color) Tasman 13 BLUE BUTTERFLY 13 Female Bath bombs/face masks (for sensitive skin) Tasman 13 BLUE BUTTERFLY 13 Female Cat necklace/earrings/bracelet
Deleted
When a wishlist is empty, then advance to the next kid
