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

Jan_18

Feb_18

Mar_18

Apr_18

May_18

Jun_18

Jul_18

Aug_18

Sep_18

Oct_18

Nov_18

Dec_18

<tbody>
</tbody>

i need the code to be this

01/01/2018

01/02/2018

01/03/2018

01/04/2018

01/05/2018

01/06/2018

01/07/2018

01/08/2018

01/09/2018

01/10/2018

01/11/2018

01/12/2018

<tbody>
</tbody>

( 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