Forum Discussion
Combining text from a large(ish) survey using a search
I have a spreadsheet created from a Form in O365 to help with judging a competition.
It has a column for group number, and another column further along for comments.
Each group will be judged concurrently by 3-4 separate sets of judges, who will each fill in the form, thus each creating a row on the spreadsheet.
Thus in the resulting spreadsheet each group will have 3 or 4 rows of data, which will not be sequential (though that I can sort, so they could be sequential, so all of group 1's rows are at the start, and group 36's rows at the end).
My job is to present the data afterwards.
What I want to do is combine the comments written about a particular group in to one cell.
I thought I could use the TEXTJOIN function. This works well, but you have to know which cells the data is in. I need a way to get the spreadsheet to say "if the group number is (say) 1, then search for those rows and join all their text bits"
I can use IFS to look for cases of group 1, and to return the comment, but only for one row. How do I then join the rows?
I need a kind of TEXTJOIN.IFS function, which MS hasn't invented yet!
Any ideas would be gratefully received!
I attach a screenshot of the data, which will be combined on another sheet.
Hi Richard,
That could be array formula (Ctrl+Shift+Enter) like
=TEXTJOIN(",",TRUE,IF(E2:E100=1,I2:I100,""))for the Group #1 and assuming your groups are in column E, texts in column I
Hi Sergei
Thanks for the speedy reply! I too had tried something like that. Unfortunately it seems to take all the comments in the (in your example) I-column, not just the ones about group 1.
Here's a simplified version of my spreadsheet.
I wwould like the comments for each group to end up in a single cell on the "test of text" sheet. You can see, I have tried various options, but the only one that works is in col. H where I type in the cell references to be used, and I want Excel to search them out!
I have come up with a not terribly elegant solution. The textjoin function can join A LOT of bits of text, so I have made a separate search for each line, and come up with:
=TEKST.KOMBINER(" - ";SAND;HVIS('RaaDataRunde1'!$E$2=$A2;'RaaDataRunde1'!$I$2;"");HVIS('RaaDataRunde1'!$E$3=$A2;'RaaDataRunde1'!$I$3;"");HVIS('RaaDataRunde1'!$E$4=$A2;'RaaDataRunde1'!$I$4;"");HVIS('RaaDataRunde1'!$E$5=$A2;'RaaDataRunde1'!$I$5;"");HVIS('RaaDataRunde1'!$E$6=$A2;'RaaDataRunde1'!$I$6;"");HVIS('RaaDataRunde1'!$E$7=$A2;'RaaDataRunde1'!$I$7;"");HVIS('RaaDataRunde1'!$E$8=$A2;'RaaDataRunde1'!$I$8;"");HVIS('RaaDataRunde1'!$E$9=$A2;'RaaDataRunde1'!$I$9;"");HVIS('RaaDataRunde1'!$E$10=$A2;'RaaDataRunde1'!$I$10;"");HVIS('RaaDataRunde1'!$E$11=$A2;'RaaDataRunde1'!$I$11;"");HVIS('RaaDataRunde1'!$E$12=$A2;'RaaDataRunde1'!$I$12;"");HVIS('RaaDataRunde1'!$E$13=$A2;'RaaDataRunde1'!$I$13;"");HVIS('RaaDataRunde1'!$E$14=$A2;'RaaDataRunde1'!$I$14;"");HVIS('RaaDataRunde1'!$E$15=$A2;'RaaDataRunde1'!$I$15;"");HVIS('RaaDataRunde1'!$E$16=$A2;'RaaDataRunde1'!$I$16;"");HVIS('RaaDataRunde1'!$E$17=$A2;'RaaDataRunde1'!$I$17;"");HVIS('RaaDataRunde1'!$E$18=$A2;'RaaDataRunde1'!$I$18;"");HVIS('RaaDataRunde1'!$E$19=$A2;'RaaDataRunde1'!$I$19;"");HVIS('RaaDataRunde1'!$E$20=$A2;'RaaDataRunde1'!$I$20;"");HVIS('RaaDataRunde1'!$E$21=$A2;'RaaDataRunde1'!$I$21;"");HVIS('RaaDataRunde1'!$E$22=$A2;'RaaDataRunde1'!$I$22;"");HVIS('RaaDataRunde1'!$E$23=$A2;'RaaDataRunde1'!$I$23;"");HVIS('RaaDataRunde1'!$E$24=$A2;'RaaDataRunde1'!$I$24;"");HVIS('RaaDataRunde1'!$E$25=$A2;'RaaDataRunde1'!$I$25;"");HVIS('RaaDataRunde1'!$E$26=$A2;'RaaDataRunde1'!$I$26;"");HVIS('RaaDataRunde1'!$E$27=$A2;'RaaDataRunde1'!$I$27;"");HVIS('RaaDataRunde1'!$E$28=$A2;'RaaDataRunde1'!$I$28;"");HVIS('RaaDataRunde1'!$E$29=$A2;'RaaDataRunde1'!$I$29;"");HVIS('RaaDataRunde1'!$E$30=$A2;'RaaDataRunde1'!$I$30;"");HVIS('RaaDataRunde1'!$E$31=$A2;'RaaDataRunde1'!$I$31;"");HVIS('RaaDataRunde1'!$E$32=$A2;'RaaDataRunde1'!$I$32;"");HVIS('RaaDataRunde1'!$E$33=$A2;'RaaDataRunde1'!$I$33;"");HVIS('RaaDataRunde1'!$E$34=$A2;'RaaDataRunde1'!$I$34;"");HVIS('RaaDataRunde1'!$E$35=$A2;'RaaDataRunde1'!$I$35;"");HVIS('RaaDataRunde1'!$E$36=$A2;'RaaDataRunde1'!$I$36;"");HVIS('RaaDataRunde1'!$E$37=$A2;'RaaDataRunde1'!$I$37;"");HVIS('RaaDataRunde1'!$E$38=$A2;'RaaDataRunde1'!$I$38;"");HVIS('RaaDataRunde1'!$E$39=$A2;'RaaDataRunde1'!$I$39;"");HVIS('RaaDataRunde1'!$E$40=$A2;'RaaDataRunde1'!$I$40;"");HVIS('RaaDataRunde1'!$E$41=$A2;'RaaDataRunde1'!$I$41;"");HVIS('RaaDataRunde1'!$E$42=$A2;'RaaDataRunde1'!$I$42;"");HVIS('RaaDataRunde1'!$E$43=$A2;'RaaDataRunde1'!$I$43;"");HVIS('RaaDataRunde1'!$E$44=$A2;'RaaDataRunde1'!$I$44;"");HVIS('RaaDataRunde1'!$E$45=$A2;'RaaDataRunde1'!$I$45;"");HVIS('RaaDataRunde1'!$E$46=$A2;'RaaDataRunde1'!$I$46;"");HVIS('RaaDataRunde1'!$E$47=$A2;'RaaDataRunde1'!$I$47;"");HVIS('RaaDataRunde1'!$E$48=$A2;'RaaDataRunde1'!$I$48;"");HVIS('RaaDataRunde1'!$E$49=$A2;'RaaDataRunde1'!$I$49;"");HVIS('RaaDataRunde1'!$E$50=$A2;'RaaDataRunde1'!$I$50;"");HVIS('RaaDataRunde1'!$E$51=$A2;'RaaDataRunde1'!$I$51;"");HVIS('RaaDataRunde1'!$E$52=$A2;'RaaDataRunde1'!$I$52;"");HVIS('RaaDataRunde1'!$E$53=$A2;'RaaDataRunde1'!$I$53;"");HVIS('RaaDataRunde1'!$E$54=$A2;'RaaDataRunde1'!$I$54;"");HVIS('RaaDataRunde1'!$E$55=$A2;'RaaDataRunde1'!$I$55;"");HVIS('RaaDataRunde1'!$E$56=$A2;'RaaDataRunde1'!$I$56;"");HVIS('RaaDataRunde1'!$E$57=$A2;'RaaDataRunde1'!$I$57;"");HVIS('RaaDataRunde1'!$E$58=$A2;'RaaDataRunde1'!$I$58;"");HVIS('RaaDataRunde1'!$E$59=$A2;'RaaDataRunde1'!$I$59;"");HVIS('RaaDataRunde1'!$E$60=$A2;'RaaDataRunde1'!$I$60;"");HVIS('RaaDataRunde1'!$E$61=$A2;'RaaDataRunde1'!$I$61;"");HVIS('RaaDataRunde1'!$E$62=$A2;'RaaDataRunde1'!$I$62;"");HVIS('RaaDataRunde1'!$E$63=$A2;'RaaDataRunde1'!$I$63;"");HVIS('RaaDataRunde1'!$E$64=$A2;'RaaDataRunde1'!$I$64;"");HVIS('RaaDataRunde1'!$E$65=$A2;'RaaDataRunde1'!$I$65;"");HVIS('RaaDataRunde1'!$E$66=$A2;'RaaDataRunde1'!$I$66;"");HVIS('RaaDataRunde1'!$E$67=$A2;'RaaDataRunde1'!$I$67;"");HVIS('RaaDataRunde1'!$E$68=$A2;'RaaDataRunde1'!$I$68;"");HVIS('RaaDataRunde1'!$E$69=$A2;'RaaDataRunde1'!$I$69;"");HVIS('RaaDataRunde1'!$E$70=$A2;'RaaDataRunde1'!$I$70;"");HVIS('RaaDataRunde1'!$E$71=$A2;'RaaDataRunde1'!$I$71;"");HVIS('RaaDataRunde1'!$E$72=$A2;'RaaDataRunde1'!$I$72;"");HVIS('RaaDataRunde1'!$E$73=$A2;'RaaDataRunde1'!$I$73;"");HVIS('RaaDataRunde1'!$E$74=$A2;'RaaDataRunde1'!$I$74;"");HVIS('RaaDataRunde1'!$E$75=$A2;'RaaDataRunde1'!$I$75;"");HVIS('RaaDataRunde1'!$E$76=$A2;'RaaDataRunde1'!$I$76;"");HVIS('RaaDataRunde1'!$E$77=$A2;'RaaDataRunde1'!$I$77;"");HVIS('RaaDataRunde1'!$E$78=$A2;'RaaDataRunde1'!$I$78;"");HVIS('RaaDataRunde1'!$E$79=$A2;'RaaDataRunde1'!$I$79;"");HVIS('RaaDataRunde1'!$E$80=$A2;'RaaDataRunde1'!$I$80;"");HVIS('RaaDataRunde1'!$E$81=$A2;'RaaDataRunde1'!$I$81;"");HVIS('RaaDataRunde1'!$E$82=$A2;'RaaDataRunde1'!$I$82;"");HVIS('RaaDataRunde1'!$E$83=$A2;'RaaDataRunde1'!$I$83;"");HVIS('RaaDataRunde1'!$E$84=$A2;'RaaDataRunde1'!$I$84;"");HVIS('RaaDataRunde1'!$E$85=$A2;'RaaDataRunde1'!$I$85;"");HVIS('RaaDataRunde1'!$E$86=$A2;'RaaDataRunde1'!$I$86;"");HVIS('RaaDataRunde1'!$E$87=$A2;'RaaDataRunde1'!$I$87;"");HVIS('RaaDataRunde1'!$E$88=$A2;'RaaDataRunde1'!$I$88;"");HVIS('RaaDataRunde1'!$E$89=$A2;'RaaDataRunde1'!$I$89;"");HVIS('RaaDataRunde1'!$E$90=$A2;'RaaDataRunde1'!$I$90;"");HVIS('RaaDataRunde1'!$E$91=$A2;'RaaDataRunde1'!$I$91;"");HVIS('RaaDataRunde1'!$E$92=$A2;'RaaDataRunde1'!$I$92;"");HVIS('RaaDataRunde1'!$E$93=$A2;'RaaDataRunde1'!$I$93;"");HVIS('RaaDataRunde1'!$E$94=$A2;'RaaDataRunde1'!$I$94;"");HVIS('RaaDataRunde1'!$E$95=$A2;'RaaDataRunde1'!$I$95;"");HVIS('RaaDataRunde1'!$E$96=$A2;'RaaDataRunde1'!$I$96;"");HVIS('RaaDataRunde1'!$E$97=$A2;'RaaDataRunde1'!$I$97;"");HVIS('RaaDataRunde1'!$E$98=$A2;'RaaDataRunde1'!$I$98;"");HVIS('RaaDataRunde1'!$E$99=$A2;'RaaDataRunde1'!$I$99;"");HVIS('RaaDataRunde1'!$E$100=$A2;'RaaDataRunde1'!$I$100;"");HVIS('RaaDataRunde1'!$E$101=$A2;'RaaDataRunde1'!$I$101;"");HVIS('RaaDataRunde1'!$E$102=$A2;'RaaDataRunde1'!$I$102;"");HVIS('RaaDataRunde1'!$E$103=$A2;'RaaDataRunde1'!$I$103;"");HVIS('RaaDataRunde1'!$E$104=$A2;'RaaDataRunde1'!$I$104;"");HVIS('RaaDataRunde1'!$E$105=$A2;'RaaDataRunde1'!$I$105;"");HVIS('RaaDataRunde1'!$E$106=$A2;'RaaDataRunde1'!$I$106;"");HVIS('RaaDataRunde1'!$E$107=$A2;'RaaDataRunde1'!$I$107;"");HVIS('RaaDataRunde1'!$E$108=$A2;'RaaDataRunde1'!$I$108;"");HVIS('RaaDataRunde1'!$E$109=$A2;'RaaDataRunde1'!$I$109;"");HVIS('RaaDataRunde1'!$E$110=$A2;'RaaDataRunde1'!$I$110;"");HVIS('RaaDataRunde1'!$E$111=$A2;'RaaDataRunde1'!$I$111;"");HVIS('RaaDataRunde1'!$E$112=$A2;'RaaDataRunde1'!$I$112;"");HVIS('RaaDataRunde1'!$E$113=$A2;'RaaDataRunde1'!$I$113;"");HVIS('RaaDataRunde1'!$E$114=$A2;'RaaDataRunde1'!$I$114;"");HVIS('RaaDataRunde1'!$E$115=$A2;'RaaDataRunde1'!$I$115;"");HVIS('RaaDataRunde1'!$E$116=$A2;'RaaDataRunde1'!$I$116;"");HVIS('RaaDataRunde1'!$E$117=$A2;'RaaDataRunde1'!$I$117;"");HVIS('RaaDataRunde1'!$E$118=$A2;'RaaDataRunde1'!$I$118;"");HVIS('RaaDataRunde1'!$E$119=$A2;'RaaDataRunde1'!$I$119;"");HVIS('RaaDataRunde1'!$E$120=$A2;'RaaDataRunde1'!$I$120;"");HVIS('RaaDataRunde1'!$E$121=$A2;'RaaDataRunde1'!$I$121;"");HVIS('RaaDataRunde1'!$E$122=$A2;'RaaDataRunde1'!$I$122;"");HVIS('RaaDataRunde1'!$E$123=$A2;'RaaDataRunde1'!$I$123;"");HVIS('RaaDataRunde1'!$E$124=$A2;'RaaDataRunde1'!$I$124;"");HVIS('RaaDataRunde1'!$E$125=$A2;'RaaDataRunde1'!$I$125;"");HVIS('RaaDataRunde1'!$E$126=$A2;'RaaDataRunde1'!$I$126;"");HVIS('RaaDataRunde1'!$E$127=$A2;'RaaDataRunde1'!$I$127;"");HVIS('RaaDataRunde1'!$E$128=$A2;'RaaDataRunde1'!$I$128;"");HVIS('RaaDataRunde1'!$E$129=$A2;'RaaDataRunde1'!$I$129;"");HVIS('RaaDataRunde1'!$E$130=$A2;'RaaDataRunde1'!$I$130;"");HVIS('RaaDataRunde1'!$E$131=$A2;'RaaDataRunde1'!$I$131;"");HVIS('RaaDataRunde1'!$E$132=$A2;'RaaDataRunde1'!$I$132;"");HVIS('RaaDataRunde1'!$E$133=$A2;'RaaDataRunde1'!$I$133;"");HVIS('RaaDataRunde1'!$E$134=$A2;'RaaDataRunde1'!$I$134;"");HVIS('RaaDataRunde1'!$E$135=$A2;'RaaDataRunde1'!$I$135;"");HVIS('RaaDataRunde1'!$E$136=$A2;'RaaDataRunde1'!$I$136;"");HVIS('RaaDataRunde1'!$E$137=$A2;'RaaDataRunde1'!$I$137;"");HVIS('RaaDataRunde1'!$E$138=$A2;'RaaDataRunde1'!$I$138;"");HVIS('RaaDataRunde1'!$E$139=$A2;'RaaDataRunde1'!$I$139;""))
It's in Danish. The first function is textjoin. The HVIS is an IF. Complicated, but it works!!
