0

I have a set of horizontal data in Excel that I need vertical. I tried the transpose formula, but when I copy the formula down the next line vertical it is not the following line.

What I need is all the data converted into one vertical line starting on A1-E1, then A2-E2, A3-E3 etc.

The formula I used is: {=TRANSPOSE($A1:$E1)} When I copy it down the next line says A5:E5 instead of A2:E2.

I have about 3000 lines, so manually it will take forever.

enter image description here

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
Torstein_norway
  • 11
  • 1

1 Answers1

1

Excel's TRANSPOSE function makes only "exact" transposition, it's not easy to combine with other transformations. Still you've a couple of options:

  1. Transpose your data line by line, including only one line in the formula for a time, repeating formula several times. As you have a lot of rows, this one probably isn't for you.
  2. Transpose the whole matrix with TRANSPOSE, then un-pivot the result.
  3. Use another formula, e.g.:
    =INDEX($A$1:$H$5,INT((ROW()-ROW($A$7))/COLUMNS($A$1:$H$5))+1,MOD(ROW()-ROW($A$7),COLUMNS($A$1:$H$5))+1)

enter image description here

Community
  • 1
Máté Juhász
  • 22,071