Search code examples
regexgoogle-sheets-formulatransposeflattentextjoin

Join range of yes/no answer header replacements values into single cell in google sheets

Replace "yes" or "no" values in this sheet a range of row (ie A2:C4) with column header value & when value (yes,no) is present then insert additional text from header text prior to all values with text for "yes" or "no" if not blank by modifying the formulas below that are explained further below.

A lot of work has been done thus far but have run into some difficulty and appreciate any help.

The desired output looks like this:

enter image description here

Goal 1: Combining Yes, No replacement formulas

  1. The current formula requires separate yes/no columns but I would like to combine the answers showing the replaced yes answers above the replaced no answers and separate with a line break.

Goal 2: Inserting additional header text at beginning of string conditionally When yes/no text is present

  1. I added text insertion based on header text and a delimiter. If "yes" replacement answer has been provided it inserts text from the left of a delimiter in header (the reverse when "no" replacement was provided). For example, a header string is provided like "Likes |Doesn't like " and then Regex

    REGEXEXTRACT(J1,"^(.*)|"

will extract the text to the left of the pipe for "yes" answers, another regex to the right for "no"

  1. I ran into a problem when I tried to create a "condition" where this would only be present if yes/no replacement was present. I did this by trying to RegExReplace this text when not followed by additional text using regex which should have worked, but did not, as shown here:

enter image description here

How can I modify the equation in this Google Spreadsheet to achieve my objective. This is based on previously answered question

Prior equation Replace "yes" answers in a range in a given row (ie A2:D4), with a header values separated by commas in E4 in an order that is desired.

Preliminary approach Replacing "no" answers in same range using a separate equation

Using a third "helper" cell to add "no" values below the yes values

Specified order The headers are labeled with a number in the desired order. The original formula will look for a "yes" and replace that with the header value for any questions in a range and outputs as an index

The second formula will then split and sort the values in alphabetical order and then REGEXREPLACE the numbers.

Any assitance to combine these three formulas to achieve above objective is much appreciated!

INDEX(REGEXEXTRACT(G1,"^(.*)\|")&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="yes", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))

and

INDEX( REGEXEXTRACT(G1,"\|(.*)")&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="no", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))

ALTERNATIVE SET #2

and

REPLACE REGEXTRACTED VALUE WHEN NOT FOLLOWED BY ADDITIONAL TEXT

The shared google sheet is here

Solution

  • use in G2:

    =ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), 
 REGEXEXTRACT(G1, "(.*)\|"), )&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",$", ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(G1, "\|(.*)"), )&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",$", "."))

    use in H2:

    =ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), 
 REGEXEXTRACT(H1, "(.*)\|"), )&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",$", ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(H1, "\|(.*)"), )&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",$", "."))

    enter image description here