Skip to main content

Join Columns as Array with separator

  • June 6, 2019
  • 6 replies
  • 77 views
K

Hi All,

We are using airtable to create a delivery schedule for our team every week. We have many columns to keep track of each product. I would like to create a summary column which lists each product that was ordered. We then export this file to import into a route planner.

We want the final output to follow this format:
Name: 123 Address, City (Prod1qty, ProdNqty) $owing {comments}

Our Columns:
Name
Address (with city)
Products Ordered
Balance
Comments

Product1
Product2
…
Product50

The trouble I’m having is with the product list. We need it to show only “Product1” if there’s just 1 and “Product1*#” if there are multiple. And I need each product to be separated with a ", " without having a coma at the end of the list.

I’m pretty sure I can use an extensive IF formula to create the list of products based on how many are ordered. I have no idea how to remove the coma at the end of the list tho. I was hoping there would be some kind of arrayjoin formula I could use to sum up the results of a bunch of columns.

I’m just at a loss for what to try next. I appreciate your help!

6 replies

J
Forum|alt.badge.img+18

I haven’t tested this, so hopefully all my commas are in the right places, and I’ve closed all my parenthesis, etc…

But I think this should at least get close to what you are wanting. You didn’t mention a name for fields that specify the quantity of the Product being purchased, so I filled that in as {Product# Quantity}. You’ll have to replace those with the name of your actual field that holds the quantity.

If it’s the actual {Product#} fields that are holding the quantity, and it’s the title of those fields that tells you the Product name, then you’ll have to change each Product conditional in the formula below to read:

IF(
   Product1,
   "Product 1" &
   IF(Product1 > 1, "*" & Product1) &
   ...

So here’s my attempt at a solution (coming in next 2 posts due to character limit):

J
Forum|alt.badge.img+18

Part 1 (1st half of the formula, continued in the post below):

IF(Name, Name & ": ") &
IF({Address (with city)}, {Address (with city)} & " ") &
IF(
    {Products Ordered},
    "(" &
    IF(
        Product1,
        Product1 &
        IF({Product1 Quantity} > 1, "*" & {Product1 Quantity}) &
        IF(
            OR(
                Product2,
                Product3,
                Product4,
                Product5,
                Product6,
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product2,
        Product2 &
        IF({Product2 Quantity} > 1, "*" & {Product2 Quantity}) &
        IF(
            OR(
                Product3,
                Product4,
                Product5,
                Product6,
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product3,
        Product3 &
        IF({Product3 Quantity} > 1, "*" & {Product3 Quantity}) &
        IF(
            OR(
                Product4,
                Product5,
                Product6,
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product4,
        Product4 &
        IF({Product4 Quantity} > 1, "*" & {Product4 Quantity}) &
        IF(
            OR(
                Product5,
                Product6,
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product5,
        Product5 &
        IF({Product5 Quantity} > 1, "*" & {Product5 Quantity}) &
        IF(
            OR(
                Product6,
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product6,
        Product6 &
        IF({Product6 Quantity} > 1, "*" & {Product6 Quantity}) &
        IF(
            OR(
                Product7,
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product7,
        Product7 &
        IF({Product7 Quantity} > 1, "*" & {Product7 Quantity}) &
        IF(
            OR(
                Product8,
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product8,
        Product8 &
        IF({Product8 Quantity} > 1, "*" & {Product8 Quantity}) &
        IF(
            OR(
                Product9,
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product9,
        Product9 &
        IF({Product9 Quantity} > 1, "*" & {Product9 Quantity}) &
        IF(
            OR(
                Product10,
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product10,
        Product10 &
        IF({Product10 Quantity} > 1, "*" & {Product10 Quantity}) &
        IF(
            OR(
                Product11,
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product11,
        Product11 &
        IF({Product11 Quantity} > 1, "*" & {Product11 Quantity}) &
        IF(
            OR(
                Product12,
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product12,
        Product12 &
        IF({Product12 Quantity} > 1, "*" & {Product12 Quantity}) &
        IF(
            OR(
                Product13,
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product13,
        Product13 &
        IF({Product13 Quantity} > 1, "*" & {Product13 Quantity}) &
        IF(
            OR(
                Product14,
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product14,
        Product14 &
        IF({Product14 Quantity} > 1, "*" & {Product14 Quantity}) &
        IF(
            OR(
                Product15,
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product15,
        Product15 &
        IF({Product15 Quantity} > 1, "*" & {Product15 Quantity}) &
        IF(
            OR(
                Product16,
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product16,
        Product16 &
        IF({Product16 Quantity} > 1, "*" & {Product16 Quantity}) &
        IF(
            OR(
                Product17,
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product17,
        Product17 &
        IF({Product17 Quantity} > 1, "*" & {Product17 Quantity}) &
        IF(
            OR(
                Product18,
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product18,
        Product18 &
        IF({Product18 Quantity} > 1, "*" & {Product18 Quantity}) &
        IF(
            OR(
                Product19,
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product19,
        Product19 &
        IF({Product19 Quantity} > 1, "*" & {Product19 Quantity}) &
        IF(
            OR(
                Product20,
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product20,
        Product20 &
        IF({Product20 Quantity} > 1, "*" & {Product20 Quantity}) &
        IF(
            OR(
                Product21,
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product21,
        Product21 &
        IF({Product21 Quantity} > 1, "*" & {Product21 Quantity}) &
        IF(
            OR(
                Product22,
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product22,
        Product22 &
        IF({Product22 Quantity} > 1, "*" & {Product22 Quantity}) &
        IF(
            OR(
                Product23,
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product23,
        Product23 &
        IF({Product23 Quantity} > 1, "*" & {Product23 Quantity}) &
        IF(
            OR(
                Product24,
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product24,
        Product24 &
        IF({Product24 Quantity} > 1, "*" & {Product24 Quantity}) &
        IF(
            OR(
                Product25,
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
J
Forum|alt.badge.img+18

Part 2 (2nd half of the formula started above):

    IF(
        Product25,
        Product25 &
        IF({Product25 Quantity} > 1, "*" & {Product25 Quantity}) &
        IF(
            OR(
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product26,
        Product26 &
        IF({Product26 Quantity} > 1, "*" & {Product26 Quantity}) &
        IF(
            OR(
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product27,
        Product27 &
        IF({Product27 Quantity} > 1, "*" & {Product27 Quantity}) &
        IF(
            OR(
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product28,
        Product28 &
        IF({Product28 Quantity} > 1, "*" & {Product28 Quantity}) &
        IF(
            OR(
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product29,
        Product29 &
        IF({Product29 Quantity} > 1, "*" & {Product29 Quantity}) &
        IF(
            OR(
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product30,
        Product30 &
        IF({Product30 Quantity} > 1, "*" & {Product30 Quantity}) &
        IF(
            OR(
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product31,
        Product31 &
        IF({Product31 Quantity} > 1, "*" & {Product31 Quantity}) &
        IF(
            OR(
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product32,
        Product32 &
        IF({Product32 Quantity} > 1, "*" & {Product32 Quantity}) &
        IF(
            OR(
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product33,
        Product33 &
        IF({Product33 Quantity} > 1, "*" & {Product33 Quantity}) &
        IF(
            OR(
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product34,
        Product34 &
        IF({Product34 Quantity} > 1, "*" & {Product34 Quantity}) &
        IF(
            OR(
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product35,
        Product35 &
        IF({Product35 Quantity} > 1, "*" & {Product35 Quantity}) &
        IF(
            OR(
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product36,
        Product36 &
        IF({Product36 Quantity} > 1, "*" & {Product36 Quantity}) &
        IF(
            OR(
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product37,
        Product37 &
        IF({Product37 Quantity} > 1, "*" & {Product37 Quantity}) &
        IF(
            OR(
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product38,
        Product38 &
        IF({Product38 Quantity} > 1, "*" & {Product38 Quantity}) &
        IF(
            OR(
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product39,
        Product39 &
        IF({Product39 Quantity} > 1, "*" & {Product39 Quantity}) &
        IF(
            OR(
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product40,
        Product40 &
        IF({Product40 Quantity} > 1, "*" & {Product40 Quantity}) &
        IF(
            OR(
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product41,
        Product41 &
        IF({Product41 Quantity} > 1, "*" & {Product41 Quantity}) &
        IF(
            OR(
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product42,
        Product42 &
        IF({Product42 Quantity} > 1, "*" & {Product42 Quantity}) &
        IF(
            OR(
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product43,
        Product43 &
        IF({Product43 Quantity} > 1, "*" & {Product43 Quantity}) &
        IF(
            OR(
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product44,
        Product44 &
        IF({Product44 Quantity} > 1, "*" & {Product44 Quantity}) &
        IF(
            OR(
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product45,
        Product45 &
        IF({Product45 Quantity} > 1, "*" & {Product45 Quantity}) &
        IF(
            OR(
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product46,
        Product46 &
        IF({Product46 Quantity} > 1, "*" & {Product46 Quantity}) &
        IF(
            OR(
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product47,
        Product47 &
        IF({Product47 Quantity} > 1, "*" & {Product47 Quantity}) &
        IF(
            OR(
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product48,
        Product48 &
        IF({Product48 Quantity} > 1, "*" & {Product48 Quantity}) &
        IF(
            OR(
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product49,
        Product49 &
        IF({Product49 Quantity} > 1, "*" & {Product49 Quantity}) &
        IF(
            Product50,
            ", "
        )
    ) &
    IF(
        Product50,
        Product50 &
        IF({Product50 Quantity} > 1, "*" & {Product50 Quantity})
    )
    ") "
) &
IF(Balance, "$" & Balance & " ") &
IF(Comments, Comments)
K
  • Kira_Arneson1
  • Author
  • Participating Frequently
  • June 7, 2019

Part 2 (2nd half of the formula started above):

    IF(
        Product25,
        Product25 &
        IF({Product25 Quantity} > 1, "*" & {Product25 Quantity}) &
        IF(
            OR(
                Product26,
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product26,
        Product26 &
        IF({Product26 Quantity} > 1, "*" & {Product26 Quantity}) &
        IF(
            OR(
                Product27,
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product27,
        Product27 &
        IF({Product27 Quantity} > 1, "*" & {Product27 Quantity}) &
        IF(
            OR(
                Product28,
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product28,
        Product28 &
        IF({Product28 Quantity} > 1, "*" & {Product28 Quantity}) &
        IF(
            OR(
                Product29,
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product29,
        Product29 &
        IF({Product29 Quantity} > 1, "*" & {Product29 Quantity}) &
        IF(
            OR(
                Product30,
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product30,
        Product30 &
        IF({Product30 Quantity} > 1, "*" & {Product30 Quantity}) &
        IF(
            OR(
                Product31,
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product31,
        Product31 &
        IF({Product31 Quantity} > 1, "*" & {Product31 Quantity}) &
        IF(
            OR(
                Product32,
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product32,
        Product32 &
        IF({Product32 Quantity} > 1, "*" & {Product32 Quantity}) &
        IF(
            OR(
                Product33,
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product33,
        Product33 &
        IF({Product33 Quantity} > 1, "*" & {Product33 Quantity}) &
        IF(
            OR(
                Product34,
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product34,
        Product34 &
        IF({Product34 Quantity} > 1, "*" & {Product34 Quantity}) &
        IF(
            OR(
                Product35,
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product35,
        Product35 &
        IF({Product35 Quantity} > 1, "*" & {Product35 Quantity}) &
        IF(
            OR(
                Product36,
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product36,
        Product36 &
        IF({Product36 Quantity} > 1, "*" & {Product36 Quantity}) &
        IF(
            OR(
                Product37,
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product37,
        Product37 &
        IF({Product37 Quantity} > 1, "*" & {Product37 Quantity}) &
        IF(
            OR(
                Product38,
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product38,
        Product38 &
        IF({Product38 Quantity} > 1, "*" & {Product38 Quantity}) &
        IF(
            OR(
                Product39,
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product39,
        Product39 &
        IF({Product39 Quantity} > 1, "*" & {Product39 Quantity}) &
        IF(
            OR(
                Product40,
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product40,
        Product40 &
        IF({Product40 Quantity} > 1, "*" & {Product40 Quantity}) &
        IF(
            OR(
                Product41,
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product41,
        Product41 &
        IF({Product41 Quantity} > 1, "*" & {Product41 Quantity}) &
        IF(
            OR(
                Product42,
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product42,
        Product42 &
        IF({Product42 Quantity} > 1, "*" & {Product42 Quantity}) &
        IF(
            OR(
                Product43,
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product43,
        Product43 &
        IF({Product43 Quantity} > 1, "*" & {Product43 Quantity}) &
        IF(
            OR(
                Product44,
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product44,
        Product44 &
        IF({Product44 Quantity} > 1, "*" & {Product44 Quantity}) &
        IF(
            OR(
                Product45,
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product45,
        Product45 &
        IF({Product45 Quantity} > 1, "*" & {Product45 Quantity}) &
        IF(
            OR(
                Product46,
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product46,
        Product46 &
        IF({Product46 Quantity} > 1, "*" & {Product46 Quantity}) &
        IF(
            OR(
                Product47,
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product47,
        Product47 &
        IF({Product47 Quantity} > 1, "*" & {Product47 Quantity}) &
        IF(
            OR(
                Product48,
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product48,
        Product48 &
        IF({Product48 Quantity} > 1, "*" & {Product48 Quantity}) &
        IF(
            OR(
                Product49,
                Product50
            ),
            ", "
        )
    ) &
    IF(
        Product49,
        Product49 &
        IF({Product49 Quantity} > 1, "*" & {Product49 Quantity}) &
        IF(
            Product50,
            ", "
        )
    ) &
    IF(
        Product50,
        Product50 &
        IF({Product50 Quantity} > 1, "*" & {Product50 Quantity})
    )
    ") "
) &
IF(Balance, "$" & Balance & " ") &
IF(Comments, Comments)

Yeah, that’s a similar version to what I have going already. I was really hoping there wouldn’t be that much code! It gets so squishy and annoying to edit when we have so many products.

I was hoping there was some kind of ARRAYJOIN([{column 1},{column 2}], ", ")

Thank you for helping, that was a lot more detailed of a response than I expected.

J
T
A
Forum|alt.badge.img+1

Now there is the ARRAYJOIN([item1, item2, item3], separator) formula.

Justin_Barrett
Forum|alt.badge.img+21

Now there is the ARRAYJOIN([item1, item2, item3], separator) formula.


Welcome to the community, @Alexey_Chekanov1! :grinning_face_with_big_eyes: Unfortunately the ARRAYJOIN() function only works on actual arrays, and at the time I’m writing this, arrays cannot be built manually by combining individual field references. Only arrays returned by other fields—namely rollup and lookup fields—will work in ARRAYJOIN().

A
Terms & ConditionsAccessibility statement