# JOINing an array based on a condition in a form-entry sheet **URL:** \ **Category:** Ask for Help\ **Created:** [March 18, 2020, 8:36pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551 "2020-03-18T20:36:24Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![apertur.co](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@apertur.co](https://community.glideapps.com/u/apertur.co)\ **Post date:** [March 18, 2020, 8:36pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/1 "2020-03-18T20:36:24Z")
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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [March 19, 2020, 12:48am UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/2 "2020-03-19T00:48:31Z")
You shouldn’t need to use a join. Just do an arrayformula like this with IF statements `A1 & ", " & B1 & ", " & C1` ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [March 19, 2020, 3:34am UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/3 "2020-03-19T03:34:05Z")
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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b0bf62e16ade0ea9b13ce278192591aeb5ae8668.png) ---
**Author:** ![apertur.co](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@apertur.co](https://community.glideapps.com/u/apertur.co)\ **Post date:** [March 19, 2020, 3:26pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/4 "2020-03-19T15:26:54Z")
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](https://stackoverflow.com/questions/14433945/arrayformula-a-filter-in-a-join-google-spreadsheets), 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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [March 21, 2020, 3:13pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/5 "2020-03-21T15:13:47Z")
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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/991a5298e1790f1d6090f171426a2a33a6bb58ac.png) `=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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/f/fbc6fe46b1e92b1cd448e089d43da7315745957f.png) 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)))` ---
**Author:** ![apertur.co](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@apertur.co](https://community.glideapps.com/u/apertur.co)\ **Post date:** [March 21, 2020, 7:11pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/6 "2020-03-21T19:11:57Z")
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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [March 21, 2020, 8:10pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/7 "2020-03-21T20:10:24Z")
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. > [@Components not showing](https://community.glideapps.com/t/components-not-showing/2776/3): > > We recently made a change in how Glide recognizes row headers. Previously Glide would guess, but sometimes the guess was wrong, and the only thing to do was to change your sheet. Now Glide will interpret frozen rows to mean that the frozen rows are the header. In your About sheet you’ve frozen the header as well as the data row, however, so the data row is interpreted as the header, and the table is empty. Just unfreeze the rows, or use a single frozen row and you should be good. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [March 21, 2020, 10:40pm UTC](https://community.glideapps.com/t/joining-an-array-based-on-a-condition-in-a-form-entry-sheet/5551/8 "2020-03-21T22:40:12Z")
Actually, I rarely add the column header to the formula. I did so in this instance just for immediate copying/pasting for a solution.