Swapping rows to colums in excel

[ashmanyeah]

Hi all,

I have downloaded a dataset and it is not consistent (in terms of how it displays the data) compared with other datasets I have. Does anyone know the most efficient way I could transform the data to look like the dataset on the right hand side? For example, I would want the years in a single column with the next column being the values for the variable in question. Obviously the country names would need to be replicated for each year, so if my dataset was from 1960 to 2010, Aruba would show up 60 times, then followed by Afghanistan etc.

If I have not explained myself sufficiently, please let me know.

Thanks

THIS IS THE IMAGE - I could not upload it to this thread for some reason (maybe too large of a filesize?)

https://postimg.org/image/h1fz6tl57/

 

[Fluff]

Hi & welcome to the board.
How about

Sub copyTrans()

   Dim Cl As Range
   Dim Hdr As Range
   Dim r As Long
   Dim ws As Worksheet
   
Application.ScreenUpdating = False
   Set ws = Sheets("pcode")
   Set Hdr = ws.Range("B1", ws.Cells(1, Columns.Count).End(xlToLeft))
   r = Hdr.Count
   For Each Cl In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
      With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)
         .Offset(1, 1).Resize(r).Value = Application.Transpose(Hdr.Value)
         .Offset(1, 2).Resize(r).Value = Application.Transpose(Cl.Offset(, 1).Resize(, r).Value)
         Cl.Copy .Offset(1).Resize(r)
      End With
   Next Cl
End Sub

 

[ashmanyeah]

Hi & welcome to the board.
How about

Sub copyTrans()

   Dim Cl As Range
   Dim Hdr As Range
   Dim r As Long
   Dim ws As Worksheet
   
Application.ScreenUpdating = False
   Set ws = Sheets("pcode")
   Set Hdr = ws.Range("B1", ws.Cells(1, Columns.Count).End(xlToLeft))
   r = Hdr.Count
   For Each Cl In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
      With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)
         .Offset(1, 1).Resize(r).Value = Application.Transpose(Hdr.Value)
         .Offset(1, 2).Resize(r).Value = Application.Transpose(Cl.Offset(, 1).Resize(, r).Value)
         Cl.Copy .Offset(1).Resize(r)
      End With
   Next Cl
End Sub

Hi there Fluff,

thanks for the swift response. Forgive me but I am unsure what I would do with that code. Is there a help page dedicated to inputting lines of code in Excel?

Kind Regards,

Ash

 

[Fluff]

It needs to go in a "Regular Module", have a look here for more details
http://www.contextures.com/xlvba01.html

 

[ashmanyeah]

It needs to go in a "Regular Module", have a look here for more details
http://www.contextures.com/xlvba01.html

Hi Fluff,

I managed to get the code pasted into the module however when I try to run it I get:

Run-time error '9':

Subscript out of range

If I press the Debug option, it points to the 'Set ws = Sheets("pcode")' line.

Thanks

 

[Fluff]

You need to change pcode to the name of the sheet containing your data.
You'll also need to change this line

With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp)

to reflect the name of the sheet you want the data copied to.

 

[ashmanyeah]

You need to change pcode to the name of the sheet containing your data.
You'll also need to change this line

With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp)

to reflect the name of the sheet you want the data copied to.

That worked a treat. Thank you kindly!

 

[Fluff]

Glad to help & thanks for the feedback