Variable for joining a header value AND/OR a delimeter to ordered values in Google Sheets (1B)

user_17835120 0
Viewed 32

In this shared Google Sheet there are values in a range, contactenated in a specified order, how can I maintain the specified order while specifying 2 variables: A) when to combine the "header value" and "cell value" into a header/value pair and B) when to add a delimiter

A header/value pair are displayed with the following equation:

    =ARRAYFORMULA(TRIM(SUBSTITUTE(TRIM(FLATTEN(QUERY(REGEXREPLACE(SORT(TRANSPOSE(
 IF((A2:D4<>"")*(REGEXMATCH(A1:D1, "^\d+_")), "♥"&A1:D1&" "&A2:D4, )), 
 FLATTEN(A1:D1), 1), "\d+_", ),,9^9))), "♥", CHAR(10)))

enter image description here

All header values with a preceding number are displayed in order in the above image.

Goals However, as in the following image, there are times when it would be useful to

  1. Not to include the header value
  1. add one or more variable delimeters (ie CHAR(10), " , " after the cell value string and other times when it would help to

enter image description here

Possible approach:

  1. In the above image, a number + an underscore ex 1_Name (B1 )can represent a parameter to include the header/value pair and a number + tilda 4~Math (A1) can represent a parameter to exclude the header value

  2. Zero or more delimeter variables (>1 represented by &)can be provided after a pipe to designate the delimiters to include afer the resulting string (value or header/variable pair)

Since the above equation, already adds a CHAR(10) delimiter I believe it would make sense to specify values after a pipe to represent a delimeter variable. As in the image below, this parameter would be inserted into the formula in that space.

enter image description here

For example, in B1 the value after the pipe CHAR(10) & CHAR(10) would add two line spaces. In A1, the value after the pipe ", " would simply add a comma without a new line space.

The shared sheet is here.

textjoin google-query-language google-sheets-formula flatten google-sheets
1 Answers

stuff in your row 1 after pipe | can be accessed only as plain text and not as formula input. therefore it's better to have , commas without double-quotes and instead of CHAR(10) & CHAR(10) use a unique symbol twice like ♦♦:

see the image for recommended changes highlighted by yellow:

enter image description here

=INDEX(SUBSTITUTE(REGEXREPLACE(SUBSTITUTE(TRIM(FLATTEN(QUERY(REGEXREPLACE(SORT(TRANSPOSE(
 TRIM(IF((A2:D4<>"")*((REGEXMATCH(A1:D1, "^\d+_"))+(REGEXMATCH(A1:D1, "^\d+~"))), 
 IFNA(REGEXEXTRACT(A1:D1, "\d+_(.*:)"))&" "&A2:D4&REGEXEXTRACT(A1:D1, "\|(.*)"), ))), 
 FLATTEN(A1:D1), 1), "\d+_", ),,9^9))), "♦ ", "♦"), ",$", ), "♦", CHAR(10)))
user_56326290
Related