How to customize order of header values that are joined when answer is yes in Google Sheets
How can I modify the equation in this Google Spreadsheet which I found as an answer to another question so that it can allow a user to customize the order of replaced values?
Goal 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 The preliminary approach uses "helper" cells in the spreadsheet (more below image), but I would appreciate help in combining two independent formulas.
Specify 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 two formulas is much appreciated!
SET #1:
=index(substitute(substitute(trim(transpose(query(transpose(if(B2:E<>"yes",,substitute(B1:E1," ","❄️"))),,9^9)))," ",", "),"❄️"," "))
AND
=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))
ALTERNATIVE SET #2
INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(B2:E="yes", B1:E1&",", )),,9^9))), ",$", ))
AND
=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))
Thank you in advance for your help!
The shared google sheet is here
use:
=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
IF(TRANSPOSE(SORT(TRANSPOSE(A2:D), FLATTEN(A1:D1), 1))="yes",
REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:D1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))
- A Regex to ensure that a string contains at least two words
- Regex: Match specific characters in any order without more occurrences of each character than specified
- bash + sed: trim trailing zeroes off a floating point number?
- Difference between ?:, ?! and ?=
- Perl search and replace words in file
- Regex to extract WordPress wp-config database information and automating it with ansible to update a shared hosting environment
- Combining regular expressions in Javascript
- Does the Swift Package Manager support regex literals?
- How to extract a substring from EDL line, between 2 sequence of characters
- RegexRenamer, how to add leading zeros?
- Understanding positive and negative lookaheads
- Regex - Does not contain '<' &' >'Characters
- How can I generate paging links from the URL when using mod_rewrite changes the URL
- Extract 12 character from a string impala query
- I am having an issue with replaceAll in MongoDB
- Using sed to delete all lines between two matching patterns
- Check for at least 1 digit and 1 letter within the first n characters
- Pattern attribute value is not a valid regular expression
- Get colon-prefixed placeholder names from a SQL string
- Extract values from a double-pipe- separated string also wrapped in pipes and square braces
- Multi-selection with regex (sublime text 2)
- Regex to match one of two words
- "The big SQL RegEx": How do I RegEx split an easy SQL query (SELECT ... INTO ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY)?
- Split string before and after double-braced placeholders
- Splitting paragraph into sentences keeping the punctuations - not a dup
- Split string by newlines
- Replace a series of the same character by its number of occurrences in the series
- How to validate 12-Hour Time with regex (regular Expression)
- How can I match a pattern as long as it's not at the beginning with regex?
- Lat/Long mask positive/negative detection