If / And Formula Help


#1

Hello

I have in my base five columns:

Product Name (starting point) (ex: Apple Macbook)

Main Color (optional)

Secondary Color (optional)

Finish (optional)

Product Variant Name (formula)

I am trying to write the following formula to generate the “Product variant name” , but having a hard time:

Product Variant Name = Main Color + “,” + Secondary Color + “,” + Finish

However, IF ANY of those three optional fields is (blank) then skip it as well as the “,” corresponding to it

My hope is to get in “product variant name” a clean product name.

How can I do so? I get lost when trying to use the IF / AND formula :frowning:


#2

Well, here’s the formula you asked for — but i suspect it’s not really what you need. (Are each of the components really optional? For instance, you would have a variant name that has no main or secondary color, but does have a finish?)

IF(
    {Main Color},
    {Main Color}
    )&
IF(
    {Secondary Color},
    IF(
        {Main Color},
        ', '&{Secondary Color},
        {Secondary Color}
        )
    )&
IF(
    {Finish},
    IF(
        OR(
            {Main Color},
            {Secondary Color}
            ),
        ', '&{Finish},
        {Finish}
        )
    )

I suspect what you’re looking for is more like this:

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )&
    IF(
        {Finish},
        ', '&{Finish}
        )
    )

This second formula will give you

Main Color
Main Color, Secondary Color
Main Color, Secondary Color, Finish
Main Color, Finish

#3

Thanks so much!! Will put it to test in the morning. Yes some products are made of materials that only have finish, not main or secondary colors, (Cast aluminum)

Thanks a million!!


#4

In that case, you might want to try the following. This will give you the four materials supported by the second formula above as well as a finish-only material. (It won’t allow a material with a secondary but no main color; if that’s a valid choice — a manufacturing version of ‘spot color,’ perhaps — you’ll need to use the original formula.)

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )
    )&
    IF(
        {Finish},
        IF(
            {Main Color},
            ', '&{Finish},
            {Finish}
            ),
        )

#5

Worked great. Thanks SO much for your help.