CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheetunpivot
Rheago
Rheago

Reputation: 99

Google Sheets Transpose Formula

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

enter image description here

Upvotes: 1

Views: 172

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15318

Try this

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

If you have an undefined number of 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

Upvotes: 1

elikoga
elikoga

Reputation: 164

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.

Upvotes: 0

Related Questions