JOINing an array based on a condition in a form-entry sheet

Ask for Help
1

I have a form in which users select members of their team using switches, i.e., each team member is TRUE or FALSE:

Alexis Ben Catherine Jenni Joe Lynelle Paul
FALSE FALSE FALSE TRUE TRUE TRUE TRUE
FALSE FALSE FALSE TRUE TRUE TRUE TRUE
TRUE TRUE FALSE TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE FALSE TRUE TRUE

I’d like to display the team members in a single text field, separate by columns:

Jenni, Joe, Lynelle, Paul
Jenni, Joe, Lynelle, Paul
Alexis, Ben, Jenni, Joe, Lynelle, Paul
Lynelle, Paul

I need it to self-populate the form-entry sheet, but ARRAYFORMULA is not compatible with JOIN. I suspect there’s a way to do this using QUERY, but I can’t figure out how.

Any suggestions?

2

You shouldn’t need to use a join. Just do an arrayformula like this with IF statements A1 & ", " & B1 & ", " & C1

3

This almost works…it puts a comma at the end of the list.

Copy/Paste this in the column after “Paul” given that the names are in columns A-G:

={"Team";ARRAYFORMULA(Trim(TRANSPOSE(SPLIT(TEXTJOIN("?",1,QUERY(TRANSPOSE(IF(SUBSTITUTE(SUBSTITUTE($A2:$G5,"TRUE",$A$1:$G$1),"FALSE","")<>"", SUBSTITUTE(SUBSTITUTE($A2:$G5,"TRUE",$A$1:$G$1),"FALSE","")&",", )), "select *", ROWS(A2:A))), "?", 0))))}

Screen Shot 2020-03-18 at 11.33.13 PM
Screen Shot 2020-03-18 at 11.33.13 PM2576×466 90.7 KB

4

Thanks for your responses, @Robert_Petitto and @Jeff_Hager. It’s not a simple JOIN, but a JOIN with a FILTER:

=if($D2="","",join(", ", filter($D$1:$J$1,$D2:$J2)))

I found one potential solution, but, frankly, I don’t understand it:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(Sheet1!B:B&" ");filter(A:A,A:A<>"")=TRANSPOSE(Sheet1!A:A))&REPT(" "&CHAR(9);TRANSPOSE(ROW(Sheet1!A:A))=ROWS(Sheet1!A:A)));CHAR(9)))))

I decided to follow the suggestion of another post in this forum to do the calculations in a second sheet that has the formula pre-pasted for, say, 1,000 rows, and pull back the results using an array formula. Because they’re in a separate sheet, they don’t count toward the row limit. Not elegant, but it works for purpose I have in mind.

5

I think it’s being overthought. I don’t think we need anything like a filter, join, or query. Should just need simple IF statements and ‘&’.

Here is a method I’m currently using in one of my apps. It’s a little different since the names are in the rows instead of in the heading. In my case the bowlers are different each week, and are chosen using choice components. I also strip out the last name for my result field.
image
=ARRAYFORMULA(IF(LEN(A2:A)=0, "", IFERROR(LEFT(A2:A, FIND(" ", A2:A)) & "| ") & IFERROR(LEFT(B2:B, FIND(" ", B2:B)) & "| ") & IFERROR(LEFT(C2:C, FIND(" ", C2:C)) & "| ") & IFERROR(LEFT(D2:D, FIND(" ", D2:D)) & "| ") & IFERROR(LEFT(E2:E, FIND(" ", E2:E)))))

For your situation, this is more of what I was thinking. It’s a lot simpler and works great with array formulas. I am showing as 2 columns to get the result for simplicity, but both formulas could be combined into one column.
image
Column E Formula:
=ARRAYFORMULA(IF(A2:A = TRUE, A1 & ", ", "") & IF(B2:B = TRUE, B1 & ", ", "") & IF(C2:C = TRUE, C1 & ", ", "") & IF(D2:D = TRUE, D1 & ", ", "") )
Column F Formula (to remove final comma):
=ARRAYFORMULA(IF(LEN(E2:E)=0, "", LEFT(TRIM(E2:E), LEN(TRIM(E2:E))-1)))

If you wanted to combine both formulas together, then use something like this. It just replaces the E2:E in the LEFT with the the formula from Column E above, so it’s basically joining the columns twice.
Once for the Trim and once to figure out the Length:
=ARRAYFORMULA(IF(LEN(A2:A)=0, "", LEFT(TRIM(IF(A2:A = TRUE, A1 & ", ", "") & IF(B2:B = TRUE, B1 & ", ", "") & IF(C2:C = TRUE, C1 & ", ", "") & IF(D2:D = TRUE, D1 & ", ", "")), LEN(TRIM(IF(A2:A = TRUE, A1 & ", ", "") & IF(B2:B = TRUE, B1 & ", ", "") & IF(C2:C = TRUE, C1 & ", ", "") & IF(D2:D = TRUE, D1 & ", ", "")))-1)))

6

Thanks, @Jeff_Hager. This is a little more complicated than I anticipated. I was hoping for something more flexible/easier to modify if I add columns or make other modifications. The second sheet approach seems to be working for now.

On a different topic, I notice your examples have formulas, but no actual data, in Row 2. What is the rationale for that? I often find I’d like to have a second header row to do intermediate calculations, but Glide automatically assumes Row 2 is usable data.

1 Like
7

I feel it’s pretty flexible. If you needed to add another column, you would simply just need to add another piece, like & IF(E2:E = TRUE, E1 & ", ", "") and that could be reduced by removing the ‘= TRUE’ as it should still check against true. Like this & IF(E2:E, E1 & ", ", ""). But if you got something that works for you, go for it.

As for the second row… Some people like to join the column heading and formula in the first row (see @Robert_Petitto’s example) . I like putting my formulas in their own row. I do it in the second row without data because if the formula is in a data row and that row is ever deleted, then you lose your formula as well. My way isolates the formula from any data. As long as you structure the array formula with an If statement, (where I check for length of a particular column) then Glide will not see the second row as it appears empty.

I tend to freeze my top 2 rows for my own reasons, but I think there is a little known feature that might work for you. Freeze your top 2 rows for your header and intermediate formula and Glide will ignore any visible data in the second row. I have not really tried that myself, but it sounds like an option that might work.

1 Like
8

Actually, I rarely add the column header to the formula. I did so in this instance just for immediate copying/pasting for a solution.

1 Like