Convert data setup

[Undertegnede]

Hi,

I'm looking to convert a large data set (up to 100' rows) from the following format:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]Cost Center
[/TD]
[TD]Project
[/TD]
[TD]Text
[/TD]
[TD]Period
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]3000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201801
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]3000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201803
[/TD]
[TD]2300
[/TD]
[/TR]
[TR]
[TD]3000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201809
[/TD]
[TD]1500
[/TD]
[/TR]
[TR]
[TD]5000
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201801
[/TD]
[TD]1100
[/TD]
[/TR]
[TR]
[TD]3500
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201802
[/TD]
[TD]1200
[/TD]
[/TR]
[TR]
[TD]5500
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201803
[/TD]
[TD]1250
[/TD]
[/TR]
[TR]
[TD]5500
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]201808
[/TD]
[TD]1700
[/TD]
[/TR]
</tbody>[/TABLE]


To the following format:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]Cost Center
[/TD]
[TD]Project
[/TD]
[TD]Text
[/TD]
[TD]201801
[/TD]
[TD]201802
[/TD]
[TD]201803
[/TD]
[TD]201804
[/TD]
[TD]201805
[/TD]
[TD]201806
[/TD]
[TD]201807
[/TD]
[TD]201808
[/TD]
[TD]201809
[/TD]
[TD]201810
[/TD]
[TD]201811
[/TD]
[TD]201812
[/TD]
[/TR]
[TR]
[TD]3000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD]2300
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3500
[/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5500
[/TD]
[TD]2000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1250
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1700
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I appreciate any assistance in creating an efficient code for the task.

Best regards,
Arild

 

[Johnny C]

In the columns with dates use the formula
=IF('SourceSheet'!$E2='DataSheet'!E$1,'SourceSheet'!$F2,0)
Assuming the source data is in a tab called SourceSheet and Period is Column E in that sheet and the bottom table is in a sheet called DataSheet and titles are in row 1 and 201801 is column E

Then copy the formula down and across

 

[Undertegnede]

In the columns with dates use the formula
=IF('SourceSheet'!$E2='DataSheet'!E$1,'SourceSheet'!$F2,0)
Assuming the source data is in a tab called SourceSheet and Period is Column E in that sheet and the bottom table is in a sheet called DataSheet and titles are in row 1 and 201801 is column E

Then copy the formula down and across

Thanks Johnny - My apologies if I misunderstood the formula, but this only solves some of what I'm trying to acheive.

The the values in column A-D also needs to follow the amount and period. Here is the code I'm using to convert the format the other way around - Maybe some with higher coding skills than me could flip the code around?

Sub Til_importformat()
Application.ScreenUpdating = False
Dim wi As Worksheet, wo As Worksheet
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
Set wi = Sheets("Input") '<-- you can change the sheet name here
Set wo = Sheets("Output") '<-- you can change the sheet name here
With wi
a = wi.Cells(1, 1).CurrentRegion
ReDim o(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 4), 1 To 6)
End With
For i = 2 To UBound(a, 1) Step 1
For c = 5 To UBound(a, 2) Step 1
j = j + 1
o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, 3): o(j, 4) = a(i, 4)
o(j, 5) = a(1, c): o(j, 6) = a(i, c)
Next c
Next i
With wo
.UsedRange.ClearContents
.Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
.UsedRange.Columns.AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub