Transpose Data From Horizontal To Vertical

dbeaty · Feb 22nd 2017

This is my first time requesting help so please have patience.

I have search the WEB and found very little help or maybe I am searching for the wrong keywords.

What’s needed is the table on the left in the attached file to be “transposed” to the table on the right. So, data stored in a horizontal table, that extends out in weeks to be “transposed \ converted” to a vertical table.

ALL help is very much APPRICIATED!

dbeaty · Feb 22nd 2017

Re: Transpose Data From Horizontal To Vertical

Sorry, said I have never done this before.

Here is the example file.

Thanks Again

MickG · Feb 22nd 2017

Re: Transpose Data From Horizontal To Vertical

Try this for results on sheet2:-

Code

Sub Trans()
Dim Ray As Variant, c As Long, n As Long, ac As Long
Ray = Range("B5").CurrentRegion
ReDim nRay(1 To UBound(Ray, 1) * (UBound(Ray, 2) - 3), 1 To 5)
 nRay(1, 1) = "Project No": nRay(1, 2) = "Code": nRay(1, 3) = "Employee Name"
 nRay(1, 4) = "Week Ending": nRay(1, 5) = "Hours"
 c = 1
 For n = 2 To UBound(Ray, 1)
    For ac = 4 To UBound(Ray, 2)
        c = c + 1
        nRay(c, 1) = Ray(n, 1): nRay(c, 2) = Ray(n, 2): nRay(c, 3) = Ray(n, 3)
        nRay(c, 4) = Ray(1, ac): nRay(c, 5) = Ray(n, ac)
    Next ac
 Next n
With Sheets("Sheet2").Range("A1").Resize(c, 5)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
End With
End Sub

Display More

Regards Mick

dbeaty · Feb 22nd 2017

Re: Transpose Data From Horizontal To Vertical

Thanks Mick, that worked great. Perfect!!!

MickG · Feb 23rd 2017

Re: Transpose Data From Horizontal To Vertical

You're welcome

raiabdal · Mar 3rd 2017

Re: Transpose Data From Horizontal To Vertical

This is great. i
had similar problem and this macro worked like a charm.
many thanks

Quote from MickG;786201

Try this for results on sheet2:-

Code

Sub Trans()
Dim Ray As Variant, c As Long, n As Long, ac As Long
Ray = Range("B5").CurrentRegion
ReDim nRay(1 To UBound(Ray, 1) * (UBound(Ray, 2) - 3), 1 To 5)
 nRay(1, 1) = "Project No": nRay(1, 2) = "Code": nRay(1, 3) = "Employee Name"
 nRay(1, 4) = "Week Ending": nRay(1, 5) = "Hours"
 c = 1
 For n = 2 To UBound(Ray, 1)
    For ac = 4 To UBound(Ray, 2)
        c = c + 1
        nRay(c, 1) = Ray(n, 1): nRay(c, 2) = Ray(n, 2): nRay(c, 3) = Ray(n, 3)
        nRay(c, 4) = Ray(1, ac): nRay(c, 5) = Ray(n, ac)
    Next ac
 Next n
With Sheets("Sheet2").Range("A1").Resize(c, 5)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
End With
End Sub

Display More

Regards Mick

MickG · Mar 3rd 2017

Re: Transpose Data From Horizontal To Vertical

You're welcome

Transpose Data From Horizontal To Vertical

Participate now!

Share