0

I'm using gsheets to structure survey responsed. I've got the list of responses (an array, user could have selected multiple). I've then seperated the unique responses to group them. Next I wanted to isolate distinct responses for each grouping so I can fugure out the unique number of each distinct grouped responses. so I created new columns in my raw data table with each grouping and then I need a formula to generate a boolean output if the user selected a response in that group.

I've got the unstructured data in column A, the unique responses and their grouping in columns B-D. In column h2 i'm trying to match the line to the grouping in h1.

=if(ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(A2, TEXTJOIN("|", 1, B:B)),B:D, 3, false)))=$H$1,TRUE,FALSE)

Using the above I'm able to do it using this staging sheet, but when I put it into the master doc (where the grouping is on a seperate sheet to the response and the ranges are alot bigger) - it doesn't work. I've put both formulas used side by side in column I and they are the same.

Sheet here : https://docs.google.com/spreadsheets/d/1Vc0s0wJ2hT7T_MmlqFpEME_9-Ya2ngFLdZJnGEFuEhQ/edit?usp=sharing

Any idea why - Is there a better way to do this?

Viktoria
  • 3
  • 1

1 Answers1

0

You may try this in Cell_H2:

=index(makearray(match(,0/(A:A<>"")),counta(H1:Q1),lambda(r,c,if(iserror(xmatch(index(H1:Q1,,c),xlookup(split(index(A2:A,r),", ",0),B:B,D:D,))),,true))))
  • Clear the range H2:Q of any existing data\formulas so as for the above formula to expand freely

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • this worked thank you so much! for anyone looking at this after - the above query also works, there was an additional space in the unstructured data response cause the matching to be wrong :) – Viktoria May 23 '23 at 16:11