Join Columns while skipping a column

Byron Hu

I'm using the join and collect functions to create a model number for a product, where it joins the base model number, color, customer and country using the following formula

=JOIN(COLLECT([Base Model Number]@row:Country@row, [Base Model Number]@row:Country@row, NOT(ISBLANK(@cell))), "-")

It works great when I have the sheet set up this way, however I would love to be able to have the color description right next to the color code, however, when I do that, I end up with a model number that looks like this:

Is there a way I can skip the color description column in the join function? Alternatively, I could move the color column after the customer and country, but would then need to have the color code be the first code joined , and then have the customer and country code added after the color code.

Thanks in advance!

Paul Newcome

There are a few ways to do this. To keep it in a single formula, you could use

=JOIN(COLLECT([Base Model Number]@row:Color@row, [Base Model Number]@row:Color@row, @cell <> ""), "-") + "-" + JOIN(COLLECT(Customer@row:Country@row, Customer@row:Country@row, @cell <> ""), "-")

Basically you are going to use a JOIN/COLLECT on the first set, a JOIN/COLLECT on the second set, then "add" them together.