Mail merge nested if statements

Copper Contributor

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

9 Replies

@RichardOlin 

 

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

@Deleted 

When a wishlist is empty, then advance to the next kid

@RichardOlin 

 

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 With

 

It 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

 

 

 

Sounds good..... eager to try it!!! Thx

@Deleted 

almost there!!!  picking up first 5 columns too... not wishlists

 

Many many thx for ur help

 

First NameFamily #SourceAgeGenderWish Item 
Olivia1 9. 10 next month!  FemalePatient 
Olivia1 9. 10 next month!  Female4.5 
Olivia1 9. 10 next month!  FemaleLarge  
Olivia1 9. 10 next month!  Female12 
Olivia1 9. 10 next month!  FemalePink 
Olivia1 9. 10 next month!  FemaleToothless and windshear from How to train your Dragon
Olivia1 9. 10 next month!  FemaleCooking, dragons, Roblox
Nicole M.2BLUE BUTTERFLY10FemaleSibling 
Madison M3BLUE BUTTERFLY10FemaleSibling 
Madison M3BLUE BUTTERFLY10Female10 womens
Madison M3BLUE BUTTERFLY10Femaleadult small
Madison M3BLUE BUTTERFLY10Femaleadult small
Madison M3BLUE BUTTERFLY10FemaleGold 
Madison M3BLUE BUTTERFLY10FemaleHarry Potter, Baby Yoda, dragons
Madison M3BLUE BUTTERFLY10FemaleReading 
Paulette N.4BLUE BUTTERFLY11FemaleSibling 
Paulette N.4BLUE BUTTERFLY11Female5 
Paulette N.4BLUE BUTTERFLY11Female12 
Paulette N.4BLUE BUTTERFLY11Female12 
Paulette N.4BLUE BUTTERFLY11FemalePink 
Paulette N.4BLUE BUTTERFLY11FemaleRapunzel, Barbie
Paulette N.4BLUE BUTTERFLY11FemaleBarbies, candy
Natalie N.  5BLUE BUTTERFLY13FemaleSibling 
Natalie N.  5BLUE BUTTERFLY13Female4.5 
Natalie N.  5BLUE BUTTERFLY13FemaleSmall 
Natalie N.  5BLUE BUTTERFLY13Female44260 
Can you send a copy of the data source to me at dougrobbinsmvp[atsymbol]gmail[dot]]com

@Deleted 

 

Perfecto!!!!!!  many thx