Convert data setup

[Undertegnede]

Hi,

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

Account	Cost Center	Project	Text	Period	Amount
3000	1000			201801	1000
3000	1000			201803	2300
3000	1000			201809	1500
5000	1200			201801	1100
3500	1200			201802	1200
5500	1200			201803	1250
5500	1200			201808	1700

<tbody>
</tbody>

To the following format:

Account	Cost Center	Project	Text	201801	201802	201803	201804	201805	201806	201807	201808	201809	201810	201811	201812
3000	1000			1000		2300						1500
5000	1200			1100
3500	1200				1200
5500	2000					1250					1700

<tbody>
</tbody>

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