Mail merge nested if statements

[RichardOlin]

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

[#2]

@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»

[#3]

@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??

[#4]

Perhaps my goal was unclear. I need one wishlist per label. Thus up to 6 labels per name

[#5]

@Deleted 

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

[#6]

@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

[#7]

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

[#8]

@Deleted 

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

Many many thx for ur help

First Name	Family #	Source	Age	Gender	Wish Item
Olivia	1		9. 10 next month!	Female	Patient
Olivia	1		9. 10 next month!	Female	4.5
Olivia	1		9. 10 next month!	Female	Large
Olivia	1		9. 10 next month!	Female	12
Olivia	1		9. 10 next month!	Female	Pink
Olivia	1		9. 10 next month!	Female	Toothless and windshear from How to train your Dragon
Olivia	1		9. 10 next month!	Female	Cooking, dragons, Roblox
Nicole M.	2	BLUE BUTTERFLY	10	Female	Sibling
Madison M	3	BLUE BUTTERFLY	10	Female	Sibling
Madison M	3	BLUE BUTTERFLY	10	Female	10 womens
Madison M	3	BLUE BUTTERFLY	10	Female	adult small
Madison M	3	BLUE BUTTERFLY	10	Female	adult small
Madison M	3	BLUE BUTTERFLY	10	Female	Gold
Madison M	3	BLUE BUTTERFLY	10	Female	Harry Potter, Baby Yoda, dragons
Madison M	3	BLUE BUTTERFLY	10	Female	Reading
Paulette N.	4	BLUE BUTTERFLY	11	Female	Sibling
Paulette N.	4	BLUE BUTTERFLY	11	Female	5
Paulette N.	4	BLUE BUTTERFLY	11	Female	12
Paulette N.	4	BLUE BUTTERFLY	11	Female	12
Paulette N.	4	BLUE BUTTERFLY	11	Female	Pink
Paulette N.	4	BLUE BUTTERFLY	11	Female	Rapunzel, Barbie
Paulette N.	4	BLUE BUTTERFLY	11	Female	Barbies, candy
Natalie N.	5	BLUE BUTTERFLY	13	Female	Sibling
Natalie N.	5	BLUE BUTTERFLY	13	Female	4.5
Natalie N.	5	BLUE BUTTERFLY	13	Female	Small
Natalie N.	5	BLUE BUTTERFLY	13	Female	44260

[#9]

Can you send a copy of the data source to me at dougrobbinsmvp[atsymbol]gmail[dot]]com

[#10]

@Deleted 

Perfecto!!!!!!  many thx