Partial string matching to index output - Google Sheets
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?
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:Qof any existing data\formulas so as for the above formula to expand freely
- How to wait for an HTTPrequest response
- Normalize Google Sheet table (1st step)
- Google Apps Script BatchUpdate appending single apostrophe to output
- Is there any way to do a bitwise shift right on a very large number in Google Sheets?
- How to calculate value based on tax bracket using Google sheet formula?
- Google script to dynamically addressing a cell in google sheet
- Add 95 to a cell if the name and date in that row match another row with the same name and date
- Auto numbering rows in GoogleSheet
- Error message while automating data entry from web app contact form to google sheet
- How do I send email to user by appending my domain name to the user name in Google Apps Script?
- How to split comma-separated values across multiple rows dynamically?
- Replace multiple strings in Google Spreadsheet with script
- How to count the amount of times a substring appears in a range of cells?
- Query with multiple data types
- Count If Range Matches Lookup in another tab
- Google Sheets Query where data in either of two columns matches the value in a cell populated by a multi-select dropdown
- Trying to combine words and math in a single cell in google sheets
- Counting the previous sample over multiple conditions throughout the range
- How to randomly select by type probability all items from a google sheet column range
- Sort result of function by RHS column
- Google Sheets Query - using another query to filter dates
- Skip formula if sheet does not exist in Google Sheets
- How to get data from public google sheet and more than 100 rows
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating