Non-Standard Transpose in Excel

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

Torstein_norway

Posted 2016-04-05T08:16:55.317

Reputation: 11

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 – 2016-04-05T08:49:34.357

1I cannot understand the question at all. you need to be more clear. – David Dai – 2016-04-05T08:55:09.097

Answers

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

Máté Juhász

Posted 2016-04-05T08:16:55.317

Reputation: 16 807

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 – 2016-04-05T11:15:05.230

1@Torstein_norway if you solved it with a macro, please post that as an answer for others to see. – Raystafarian – 2016-04-05T11:48:08.963

Asked: 2016-04-05T08:16:55.317

Viewed: 120 times

Active: 2016-04-05T11:48:33.183