arraysgoogle-sheetsgoogle-sheets-formulatransposeflatten
Split strings in a google sheet cell into multiple rows?
In this thread I got the correct answer, but when I delete the values in columns B and C there are no results
Here is the google sheets formula
=INDEX(IFERROR(SPLIT(FLATTEN(FLATTEN(
FILTER(A2:A; A2:A<>"")&"×"&TRANSPOSE(
FILTER(B2:B; B2:B<>"")))&"×"&TRANSPOSE(
FILTER(C2:C; C2:C<>""))), "×"))
INPUT
| A | B | C |
|---|---|---|
| car | red | AA |
| train | yellow | BB |
| blue | CC | |
| DD |
OUPUT
I want when I delete the values in B and C to have the result like below.
| A |
|---|
| car |
| train |
If I just delete the result in column C, I get the result with 6 rows like below.
| A | B |
|---|---|
| car | red |
| car | yellow |
| car | blue |
| train | red |
| train | yellow |
| train | blue |
Solution
use:
=INDEX(SPLIT(FLATTEN(FLATTEN(
FILTER(A2:A, A2:A<>"")& IF(LEN(TRIM(QUERY(B2:B,,9^9))),"×"&TRANSPOSE(
FILTER(B2:B, B2:B<>"")), ))&IF(LEN(TRIM(QUERY(C2:C,,9^9))),"×"&TRANSPOSE(
FILTER(C2:C, C2:C<>"")), )), "×"))
- 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