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!

Source Data Structure
Cost Center	Organization	Jan - Cost	Jan - Plan	Feb - Cost	Feb - Plan	March - Cost	March - Plan
A	Claims	23	23	34	25234	2345	6
B	Claims	26	2345	234	7	235	6
C	Product	245	25	1235	235	6	6

<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

Desired Data Structure

Month	Cost Center	Organization	Cost	Plan
1/1/2016	A	Claims	23	23
1/1/2016	B	Claims	26	2345
1/1/2016	C	Product	245	25
2/1/2016	A	Claims	34	25234
2/1/2016	B	Claims	234	7
2/1/2016	C	Product	1235	235
3/1/2016	A	Claims	2345	6
3/1/2016	B	Claims	235	6
3/1/2016	C	Product	6	6

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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