Transpose Data row to Diagonal in Google Spreadsheet

Question

I've tried to find a Google Spreadsheet formula that would transpose the rows of, say, a 5-column array to the diagonal, like this (assuming data in e.g. A1:E20)

A1

A2 B1

A3 B2 C1

A4 B3 C2 D1

A5 B4 C3 D2 E1

A6 B5 C4 D3 E2

A7 B6 C5 D4 E3
...

I've found solutions for the opposite problem (from diagonal to row or column - many thanks for helping) but not for this one.

Your help is much appreciated!

You probably found 'the solution for the opposite problem' in this post: http://stackoverflow.com/questions/41741320/transpose-diagonal-to-row-in-google-spreadsheet ? If the answer provided there, answers your question (in that post), you can at least accept the answer ? — JPV, Jan 19 '17 at 20:21
Correct, JPV! Sorry, I did not know there was an accept answer feature. I added a thank you answer but it evidently got deleted. Anyway problem solved, thanks! — nickros, Jan 20 '17 at 08:29

Pyroglyph · Answer 1 · 2017-01-24T03:22:40.140

0

Edit:

I deleted my old comment because I misread your question. The following code works for all cells your just need to drag to size. The code assumes a starting row of one and column of 11, but that can be adjusted to any value.

=if((row()-(column()-11))>0,indirect(char(column()-10+64)&row()-(column()-11)),"")

Edit 2:

So to make this completely variable, you need only know the distance between your top left cell of the original data and the top left cell of the new block. Lets call the number of columns difference "dx" the number of rows difference "dy"

=if(((row()-dy)-(column()-dx))>0,indirect(char(column()-dx+65)&(row()-dy)-(column()-dx)),"")

just replace dX and dy with numbers, or cell references, and off you go!

Edit 3:

Last one I promise, this one fixes the blank cell at the top row (though I was only getting one)

=if(((row()-dy+1)-(column()-dx))>0,indirect(char(column()-dx+64)&(row()-dy+1)-(column()-dx)),"")

edited Jan 24 '17 at 03:22

answered Jan 19 '17 at 23:14

Pyroglyph

226
2
6

Thanks! This works, but I have a problem adjusting the formula if it is placed elsewhere in the sheet. Especially if the array of transposed cells should start on a different row than the original data. – nickros Jan 20 '17 at 08:52
Thanks again! I tried your second edit, and now it allows free placement of the new block. But I still have some problems. By changing the "dx+65" to "dx+64" I got it to work , however, the data in the new block is pushed down 3 rows relative to its top left cell, where the formula is placed. I guess I can live with that, however. – nickros Jan 22 '17 at 12:45

score 0 · Accepted Answer · answered Jan 20 '17 at 15:25

0

Here's another way that goes back to your original post

=iferror(if(columns($A1:A1)>rows(A$1:A1),"",index(A$1:A$5,rows(A$1:A1)-columns($A1:A1)+1)),"")

answered Jan 20 '17 at 15:25

Tom Sharpe

30,727
5
24
37

score 0 · Answer 3 · answered Jun 18 '20 at 11:58

Here is an ARRAYFORMULA for that task. The only parameter is the range itself (A1:E20) which is easy to change.

Place it in just one cell:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      SEQUENCE(ROWS(A1:E20) + COLUMNS(A1:E20) - 1) - (COLUMN(A1:E20) - 1),
      {ROW(A1:E20), A1:E20},
      SEQUENCE(1, COLUMNS(A1:E20), 2, 1),
      0
    )
  )
)

Transpose Data row to Diagonal in Google Spreadsheet

3 Answers3