Using Join with Logic

Donni Matthew

Is there a way in Smartsheet to combine the data in the rows below AND include the Column Headers AND exclude the "No Impact" Status?

The end result should look something like this:

Paul Newcome

Do you want it all in a single cell, or would it work if the Area was in one column and the Impact in another so long as the Impact lined up with the Area?

Donni Matthew

One cell would be preferable, but we could probably get by with 2 if necessary.

Paul Newcome

Using two separate columns is actually going to be the easiest way to go. It can be done in a single cell, but that would require a mess of a nested IF.

To do this with more simple formulas in two different cells, we would first need to start by including a helper ROW. You can set the font color to match the fill color so it is not plainly visible and lock the row to prevent accidental editing. FOr this example, I am going to use row 1, but you can use whatever row you want.

In this helper row, you are going to duplicate the column names. Formulas can't reference the column names directly, so we need to give the formulas some text to pull.

Next we move on to the [Areas to Display] column where we are going to put our first formula.

A few notes about this...

I am going to use locked in row references and @row references in the same formula. This is intentional. The basic idea is that we are going to JOIN the data across the helper row ($1) for all columns that are not "No Impact" on the current row the formula is residing on. We are going to use a colon followed CHAR(10) as the delimiter for the JOIN function. CHAR(10) is a line break and (when text wrap is enabled) will provide the display format you are looking for.

=JOIN(COLLECT([Impact on Referrals]$1:[Impact on Recording Documents]$1, [Impact on Referrals]@row:[Impact on Recording Documents]@row, @cell <> "No Impact"), ":" + CHAR(10))

Next we use the same logic, but we pull from @row instead of row $1 to generate our impacts. Using the CHAR(10) function will provide those same line breaks so that each entry lines up with the Areas cell.

=JOIN(COLLECT([Impact on Referrals]@row:[Impact on Recording Documents]@row, [Impact on Referrals]@row:[Impact on Recording Documents]@row, @cell <> "No Impact"), CHAR(10))

Putting the first formula in a cell immediately to the left of the cell containing the second formula and adjusting the format so that the left column is right justified and the right column is left justified should give you what you are looking for.