arraysgoogle-sheetstransposeflattengoogle-query-language
Convert columns to rows and repeat row labels in google sheets
I am trying to convert table 1 to the format in table 2. How can I do it in google sheets ? Thank you very much.
Table 1 -
Table 2 -
Solution
use:
=ARRAYFORMULA(QUERY(TO_TEXT(SPLIT(FLATTEN(
A3:A&"×"&B2:E2&"×"&TEXT(B1:E1, "dd/mm")&"×"&B3:E), "×")),
"where Col4 is not null", ))
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines
- Add prefix to array elements then join into a string
- Parse square brace tagged text into an associative array
- Parse an SQL CALL statement and create an array with its parameters
- How to insert an item at the beginning of an array in PHP?
- Parse inline style declaration string to array
- How to swap the positions of Min and Max in an array?
- Combining two arrays to form a JavaScript object
- Cannot memory-free strings from arbitrary-length array of strings
- Is a char array more efficient than a char pointer in C?
- Split the comma-separated string of a specified column in each row of a multidimensional array