CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formula
user3871
user3871

Reputation: 12708

Rotate column B, C, etc values based on values in column A

I'm aware in Python pandas you can do melt to rotate cell values.

Here I want to rotate column B and C values based on column A:

enter image description here

Desired Result:

enter image description here

I need this to update on the fly as I change other values in the Google sheet, otherwise, I'd export this to a pandas dataframe and melt from there.

How can I "melt" in Google Sheets such that this rotation will update on the fly? If there's no built-in function, how would I do this in Google Sheets javascript?

Note: Transpose (=TRANSPOSE(A2:C2)) seems promising, but it doesn't pivot the table, it only moves the cell values in B and C beneath A:

enter image description here

Upvotes: 0

Views: 220

Answers (4)

Amit S
Amit S

Reputation: 1

={{A1:A2,B1:B2};{A1:A2,C1:C2};{A1:A2,D1:D2}}

Add as many columns in this array. I have assumed the data to have 4 columns, with column A being the leading column.

snapshot of the data and the solution

Upvotes: 0

player0
player0

Reputation: 1

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B1:C<>"", "♠"&A1:A&"♦"&B1:C, )), , 999^99)), , 999^99), "♠")), "♦")))

0

Upvotes: 1

TheMaster
TheMaster

Reputation: 50416

You can use the typical join split combo

=ARRAYFORMULA(SPLIT(A2&"🔺"&TRANSPOSE(B2:C2),"🔺"))

Upvotes: 0

dwmorrin
dwmorrin

Reputation: 2734

Example on Sheets.

screenshot of example

Row 1 contains "hello world" "stringA" "stringB" etc. in columns A,B,C, etc.

I'm starting the output at row 3. (Outputs to columns A, B, starting on row 3.)

You can use transpose in the second column, e.g. place your transpose in B3 like this:

=TRANSPOSE(B1:1)

And then your static value for column A can be achieved with this in A3:

=ARRAYFORMULA(if(NOT(ISBLANK(B3:B)),$A$1,""))

which says to keep on eye on column B, starting at row 3, and add the static value from A1 if there is something in column B.

This dynamically grows the vertical list as you add values in row 1, and the static value follows the value of A1.

Upvotes: 1

Related Questions