CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Simon GIS
Simon GIS

Reputation: 1055

Google Sheets Transpose last columns values

I have this table in Google Sheets

Month   1  2  3  ...
1      20 30 45
2         32 47
3            53
...

How do I Transpose the last value of each columns into this?

Month  lastValue
 1       20
 2       32
 3       53

...

Upvotes: 0

Views: 53

Answers (1)

Tanaike
Tanaike

Reputation: 201553

Although I'm not sure whether I could correctly understand your question, in your situation, how about the following sample formula?

Sample formula:

=BYROW(B2:D,LAMBDA(x,IFERROR(INDEX(SPLIT(TEXTJOIN(",",TRUE,x),","),1))))

  • In this formula, in order to start no empty cell, I used TEXTJOIN and SPLIT. And, the 1st cell is retrieved. I used this with BYROW.

  • As another approach, this formula =BYROW(B2:D,LAMBDA(x,IFERROR(INDEX(FILTER(x,x<>""),1)))) might be able to be also used.

Testing:

When this formula is used in your provided situation, the following result is obtained.

enter image description here

References:

Upvotes: 1

Related Questions