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,583
  • 11
  • 60
  • 89
Torstein_norway
  • 11
  • 1
  • it seems from your picture that you aren't looking for a standard transpose, but try to convert all data in one column. Is it right? Please also post the formula you've tried. Please don't respond in answer, but edit your question. – Máté Juhász Apr 05 '16 at 08:49
  • 1
    I cannot understand the question at all. you need to be more clear. – SparedWhisle Apr 05 '16 at 08:55

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
  • 21,403
  • 6
  • 54
  • 73
  • Thanks. It didnt work as I hoped it would, but I see that I have not been clear enough. Sorry about that. I discussed it with a college of mine, and we ended up writing s sub: Sub TranposeToSheet() Dim sh As String sh = "Sheet2" With ActiveSheet Max = .UsedRange.Rows.Count For x = 1 To Max For y = 1 To 4 Worksheets(sh).Cells((x * 4) + y, 1) = .Cells(x, y + 1) ' Value Worksheets(sh).Cells((x * 4) + y, 2) = .Cells(x, y + 4 + 1) ' Price Next Next End With End Sub This worked as a peach. Thanks anyway! – Torstein_norway Apr 05 '16 at 11:15
  • 1
    @Torstein_norway if you solved it with a macro, please post that as an answer for others to see. – Raystafarian Apr 05 '16 at 11:48