Dates Separated into Columns (e.g. 2016, 2017) into a single Column called 'Year'

[moss84]

Hello all - I am really struggling with some ETL and I was wondering if anyone has a good way either with VBA or a built-in excel tool to transform a data set I've been working on. My problem is this: I have a source data set that has months separated into multiple columns with a different month & measure in each column (see below). I want to transform this data such that months and the metrics become their own columns. Trend analysis is very difficult to do when the information is structured in the first scenario. Please help!
[TABLE="width: 601"]
<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2"]Source Data Structure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost Center[/TD]
[TD]Organization [/TD]
[TD]Jan - Cost[/TD]
[TD]Jan - Plan[/TD]
[TD]Feb - Cost[/TD]
[TD]Feb - Plan[/TD]
[TD]March - Cost[/TD]
[TD]March - Plan[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Claims[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]25234[/TD]
[TD="align: right"]2345[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Claims[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2345[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Product[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]


Desired Data Structure
[TABLE="width: 357"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Cost Center[/TD]
[TD] Organization[/TD]
[TD] Cost[/TD]
[TD] Plan[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2016[/TD]
[TD] A[/TD]
[TD] Claims[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2016[/TD]
[TD] B[/TD]
[TD] Claims[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2345[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2016[/TD]
[TD] C[/TD]
[TD] Product[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD] A[/TD]
[TD] Claims[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]25234[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD] B[/TD]
[TD] Claims[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD] C[/TD]
[TD] Product[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD] A[/TD]
[TD] Claims[/TD]
[TD="align: right"]2345[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD] B[/TD]
[TD] Claims[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD] C[/TD]
[TD] Product[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this for results on sheet2

[COLOR="Navy"]Sub[/COLOR] MG12Aug57
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Date
Ray = Range("A1").CurrentRegion
ReDim nRay(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 5)
c = 1
nRay(1, 1) = "Month": nRay(1, 2) = "Cost Center": nRay(1, 3) = "Organization": nRay(1, 5) = "Plan"
[COLOR="Navy"]For[/COLOR] Ac = 3 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
    Sp = Split(Ray(1, Ac), " - ")
    Dt = DateSerial("2016", mth(Sp(0)), "1")
        [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
            c = c + 1
            nRay(c, 1) = Dt
            nRay(c, 2) = Ray(n, 1)
            nRay(c, 3) = Ray(n, 2)
            nRay(c, 4) = Ray(n, Ac)
            nRay(c, 5) = Ray(n, Ac + 1)
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 5)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Function mth(m [COLOR="Navy"]As[/COLOR] Variant) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
m = Left(m, 3)
[COLOR="Navy"]For[/COLOR] n = 1 To 12
   [COLOR="Navy"]If[/COLOR] MonthName(n, True) = m [COLOR="Navy"]Then[/COLOR]
        mth = n
        [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] Function

Regards Mick