1

How can I convert the data in the red cell to the data in the green cell?

enter image description here

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Rheago
  • 99
  • 6
  • Use [tables](https://meta.stackexchange.com/questions/356997/) to show your data structure. You can easily create a table using [this script](https://stackoverflow.com/a/66772209/) or the formula: `=ARRAYFORMULA("|"&A1:G20)`, if you want to share `A1:G20`. If you use formula, the second row `A2:G2` should only contain dashes `--` in all the cells, while `A1:G1` acts as a header. If you share spreadsheets, do note that [your email address can be accessed by the public](https://meta.stackoverflow.com/questions/394304/). – TheMaster Dec 20 '21 at 16:45

2 Answers2

1

Try this

=query(arrayformula(split(flatten(A2:A3&"|"&B2:F3),"|")),"select * where Col2 is not null")

if you have an undifined number fo rows, try

=query(arrayformula(split(flatten(A2:A&"|"&B2:F),"|")),"select * where Col2 is not null")

but limit the rows of the sheet to prevent long calculation

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
0

Use the TRANSPOSE Function documented here: https://support.google.com/docs/answer/3094262?hl=en

Sample Usage

TRANSPOSE({1,2;3,4;5,6})

TRANSPOSE(A2:F9)

Syntax

TRANSPOSE(array_or_range)

`array_or_range` - The array or range whose rows and columns will be swapped.
elikoga
  • 164
  • 8