Macro to Reformat/transpose data into table style

[ANTONIO1981]

Hi All


the attached file contains 2 tabs employee table macro.xlsx - Google Drive

1. assumptions

The user will populate the data in the yellow area, please take into account that the number of rows will increase substantially

2. final_results

the macro needs to take the data from tab assumptions and combine it in order to create the data shown in tab "final_results"


thanks in advance

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG10Aug36
[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] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Sheets("assumptions").Range("A1").CurrentRegion.Resize(, 17)
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2) + 1, 1 To 6)
c = 1
 nray(c, 1) = Ray(1, 1)
 nray(c, 2) = Ray(1, 2)
 nray(c, 3) = Ray(1, 3)
 nray(c, 4) = Ray(1, 4)
 nray(c, 5) = "Date"
 nray(c, 6) = "Amount"
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
     [COLOR="Navy"]For[/COLOR] Ac = 5 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
        nray(c, 3) = Format(Ray(n, 3), "mmm_yy")
        nray(c, 4) = Ray(n, 4)
        [COLOR="Navy"]If[/COLOR] IsDate(Ray(1, Ac)) [COLOR="Navy"]Then[/COLOR]
                nray(c, 5) = Format(Ray(1, Ac), "mmm_yy")
        [COLOR="Navy"]Else[/COLOR]
                nray(c, 5) = Ray(1, Ac)
        [COLOR="Navy"]End[/COLOR] If
        nray(c, 6) = Format(Ray(n, Ac), "#,##0")
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
Sheets("final_results").Range("A1").Resize(c, 6) = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ANTONIO1981]

HI mICK


almost perfect

your code gives

[TABLE="width: 768"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Jan_18[/TD]
[TD="class: xl65, width: 64"]Feb_18[/TD]
[TD="class: xl65, width: 64"]Mar_18[/TD]
[TD="class: xl65, width: 64"]Apr_18[/TD]
[TD="class: xl65, width: 64"]May_18[/TD]
[TD="class: xl65, width: 64"]Jun_18[/TD]
[TD="class: xl65, width: 64"]Jul_18[/TD]
[TD="class: xl65, width: 64"]Aug_18[/TD]
[TD="class: xl65, width: 64"]Sep_18[/TD]
[TD="class: xl65, width: 64"]Oct_18[/TD]
[TD="class: xl65, width: 64"]Nov_18[/TD]
[TD="class: xl65, width: 64"]Dec_18

[/TD]
[/TR]
</tbody>[/TABLE]

i need the code to be this

[TABLE="width: 900"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]01/01/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/02/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/03/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/04/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/05/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/06/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/07/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/08/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/09/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/10/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/11/2018[/TD]
[TD="class: xl65, width: 75, align: right"]01/12/2018[/TD]
[/TR]
</tbody>[/TABLE]


( i had format it as mmm-yy perhaps has confused you, sorry...)


thanks in advance
AC

 

[MickG]

Try changing the line below:-
NB:- I should reformat the Resuts sheet to "General" before running the code !!!

If IsDate(Ray(1, Ac)) Then
                nray(c, 5) =[B][COLOR=#B22222] CDate(Ray(1, Ac))[/COLOR][/B]
        Else
                nray(c, 5) = Ray(1, Ac)
        End If

 

[ANTONIO1981]

many thanks you are a super star! it works perfect

 

[MickG]

You're welcome