Help removing blanks from a join collect formula

System

This discussion was created from comments split from: Need help removing blanks from a join collect formula.

AnneSh

Im Trying this but I cant seem to make it work. I also tried to select a column but for some reason I am not able to select a column.

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row), ",")

=JOIN(COLLECT([SAT A]@row:[SAT 1], @cell<>"", [SAT A]@row:[SAT ID]@row), ",")

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, @cell <> "", [SAT A]@row:[SAT 1]@row, ","))

Still comes back UNPARSEABLE or INCORRECT ARGUMENT

KPH

Hi @AnneSh

1)This formula

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row), ",")

Needs a criterion range and criteria in the COLLECT function

Like this

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, criterion range, criteria), ",")

2)This formula

=JOIN(COLLECT([SAT A]@row:[SAT 1], @cell<>"", [SAT A]@row:[SAT ID]@row), ",")

Is missing

@row after [SAT 1]

And although it does have the right number of elements they aren't in the right order. We'll come back to it.

3)This formula

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, @cell <> "", [SAT A]@row:[SAT 1]@row, ","))

Has the missing @row but the same sequence issues as the second one, and also has the closing parenthesis for the COLLECT after the delimiter for the JOIN.

I think formula 2 is the closest to what you want to achieve. If we add the @row and swap the last two components around so the criteria range is before the criteria, we have this:

=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, [SAT A]@row:[SAT 1]@row, @cell <> ""), ",")

This will output the values of the cells between SAT A and SAT 1 in the current row, where those cells are not blank. I hope that is what you are looking for.