How to move monthly data in columns to rows

Category: [Excel] Demo Available

How to move monthly data in columns to rows

Requirement:

The user needs to create a standard excel file that will allow users to take their monthly budgets that appear in columns and convert this to data that appears in rows (it's a load file for an in house software).

This is what the data currently would look like:

Project	$ Type	Resource	01-01-2018	02-01-2018	03-01-2018
500	Operating	EXTSVC	500.00	550.00	605.00
600	Capital	INTL	1000.00	1100.00	1210.00
700	Capital	EXTSVC	1500.00	1650.00	1815.00
800	Capital	INTL	2000.00	2200.00	2420.00
900	Capital	EXTSVC	2500.00	2750.00	3025.00
900	Operating	EXTSVC	3000.00	3300.00	3630.00

This what the data needs to look like:

Project	$ Type	Resource	Date	Amount
500	Operating	EXTSVC	01-01-2018	500.00
500	Operating	EXTSVC	02-01-2018	550.00
500	Operating	EXTSVC	03-01-2018	605.00
600	Capital	INTL	01-01-2018	1000.00
600	Capital	INTL	02-01-2018	1100.00
600	Capital	INTL	03-01-2018	1210.00
700	Capital	EXTSVC	01-01-2018	1500.00
700	Capital	EXTSVC	02-01-2018	1650.00
700	Capital	EXTSVC	03-01-2018	1815.00
800	Capital	INTL	01-01-2018	2000.00
800	Capital	INTL	02-01-2018	2200.00
800	Capital	INTL	03-01-2018	2420.00
900	Capital	EXTSVC	01-01-2018	2500.00
900	Capital	EXTSVC	02-01-2018	2750.00
900	Capital	EXTSVC	03-01-2018	3025.00
900	Operating	EXTSVC	01-01-2018	3000.00
900	Operating	EXTSVC	02-01-2018	3300.00
900	Operating	EXTSVC	03-01-2018	3630.00

Is there are any easy way to accomplish this?

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1211615-monthly-data-in-columns-needs-to-be-moved-to-rows

Solution:

Code:

 Sub test()
   dim x,i&,ii&, j&,z()
   x =[A1].CurrentRegion.value
   redim z(1 to ubound(x) * 4,1 to 5)
    for i = 2 to Ubound(x)
          for ii = 4 to 6
               j = j + 1
               z(j,1) =x(i,1)
               z(j,2) =x(i,2)
               z(j,3) =x(i,3)
               z(j,4) =x(1,ii)
               z(j,5) =x(i,ii)
          next ii
     next i
with sheets.add.[A1]
 .resize(,5)=array("project","type","resource","date","amount")
 .offset(1).resize(ubound(z,1),5) = z
end with
End sub

Obtained from the OzGrid Help Forum.

Solution provided by graha_karya.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.