Transpose only few columns in excel and add as new rows

[harira]

[TABLE="width: 901"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
Can you please suggest to transpose this data in excel

Actual [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Published[/TD]
[TD]Forecast[/TD]
[TD]ZDP[/TD]
[TD]ZST[/TD]
[TD]ZMAT[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Expected [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Published[/TD]
[TD]Forecast[/TD]
[TD]ZDP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Published[/TD]
[TD]Forecast[/TD]
[TD]ZST[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Published[/TD]
[TD]Forecast[/TD]
[TD]ZMAT
[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance .
Hari

 

[hiker95]

harira,

Welcome to the MrExcel forum.

We can not tell what worksheet(s), cells, rows, columns, your raw data is in.


It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have assumed that the data to be transposed starts in cell A2 and that there is actually more than one row of it, try this in a copy of your workbook.

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2", Range("A2").End(xlDown)).Resize(, Cells(2, Columns.Count).End(xlToLeft).Column).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a) * (uba2 - 2), 1 To 3)
  For i = 1 To UBound(a)
    For j = 3 To uba2
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, j)
    Next j
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(2).Resize(UBound(b), 3).Value = b
End Sub

 

[alansidman]

An alternative is to use Power Query which is include in 2016 and 365. If you have an earlier version of Excel, you may download from MS for free.

In PQ, add your table using Get and Transform. Highlight the three columns you wish to transform and right click and then Unpivot. This will generate the expected results.