Combine column data into one cell based on the status

Krystin

I have a grid template that tracks the status of program implementation by state. I am trying to pull all of the live states into one cell if the status of the row is launched. Is there anyone who has been able to do something similar with a reliable formula?

I have built something similar on another template but the formula maxes out at 50 rows and breaks when a row is deleted. I would like to avoid using this finicky formula again. Any help would be greatly appreciated! Thank you!

Paul Newcome

I would suggest this in the sheet summary field:

=JOIN(DISTINCT(COLLECT(State:State, Status:Status, @cell = "Launched")), CHAR(10))

Mark.poole

@Krystin

Could we get a screenshot cleaned of sensitive data for a little more context. Are all your states in separate rows or separate columns. Are they in a single Multi select drop down column.

bisaacs

Hey @Krystin,

Maybe something like this as a column formula would work:

=IFERROR(IF(Status@row = "Launched", JOIN([Column Range]:[Column Range], ", "), "Blank")

Hope this helps!

Krystin

The states are all in one column with a column time of Text/Number and it is the primary column. So for the rows that have a status of Launched, I want those states in one cell. My plan was to do this on the sheet summary but open to other suggestions.

So in the example above the result would be TX MI FL.

Paul Newcome

I would suggest this in the sheet summary field:

=JOIN(DISTINCT(COLLECT(State:State, Status:Status, @cell = "Launched")), CHAR(10))

Krystin

That is the solution! THANK YOU!!!!!!!!!!!!

Paul Newcome

Happy to help. 👍️