Customizing order of joined cell + header values when cell value isn't blank in Google Sheets (1A)
How can I modify the equation in this Google Spreadsheet so that a user can customize the order of header & Value pairs?
Goal 1: Order display based on the number before a header. Goal 2: Don't display the header/value pair when there is no number present in the header (alt: when a wildcard value is presented before the header don't display)
This equation results in the following display
INDEX(TRIM(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(A2:D6="",,"♦"&A1:D1&" "&A2:D6)),,9^9)), "♦", CHAR(10))))
However, it would be preferable if the order could be arranged based on providing an order in the header value like the following display where empty cells (where ISBLANK=TRUE) are not displayed (2 & 3). Also, the header/value pair for "History" are not displayed since a _History is provided in the column header without a number.
I believe the following formula related to another question, which reordered headers (but also did other functions) can be modified to achieve the desired function
INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
IF(TRANSPOSE(SORT(TRANSPOSE(A9:D10), FLATTEN(A8:D8), 1))="yes",
REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A8:D8))), "(^\d+_)", )&"", )),,9^9))), ",$", ))
The shared spreadsheet is Google Spreadsheet is here
Try:
=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))))
- How to remove last element from array?
- Does Kotlin support string/array slices by reference like Rust and Swift?
- Sort a 2d array by a column and obey a dynamic direction variable
- Sort a flat array ascending
- Calling sort() inside of a foreach() does not affect the original array
- Loop only stores the last row while trying to build a 2d array while looping a query result set
- Populate a 2d array from a query result and use a column value as the first level keys
- How to have a link that has a product id in it
- how to declare punch of array variable in Excel using PHPExcel??/
- Populate a grouped multidimensional array from parsed HTML content
- Sort rows of a 2d array by its first column descending
- Duplicate and append each row of a 2d array using a flat aray
- Parse a text file into an array of associative arrays by splitting on semicolons, then commas, then equals signs
- MiniZinc Assign Different Domain to Each Var in Array
- Convert string back to array of tuples
- How to create a multidimensional array with an identifying column value used as the first level key?
- How do I export an array with module.exports in node js?
- Why do C and C++ support memberwise assignment of arrays within structs, but not generally?
- How to create a multi-dimensional array of each type of input letter instead of one big array
- How to merge two arrays in a zipper like fashion in Bash?
- Sort rows of a 2d array by address column prioritizing street name, then odd street numbers before evens, then street numbers
- Sort rows of a 2d array by a column's leading number so that odd numbers come before even numbers
- Sort a flat array to alternate values
- Declare a multidimensional array by using the values of a flat array as the keypath to a pre-defined value
- How to reindex an array of objects?
- How to add a new row to a pre-existing 2d array?
- Create mutable List from array?
- How to build a 2d array with static and dynamic data while looping?
- Push two elements into the same level of an array
- Laravel implode function on images array