'Un-pivoting' normal table/matrix

[maximus510]

Hi everyone,

I have a matrix of months and years (hardcoded) which I want to list out in 1 big list, 'unpivoting' so as there are, for example below, 3 "30/04/22" listed, then 3 "31/05/22" etc etc for the whole matrix. The list would be 83 rows which sums to the matrix:-

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2022	0	0	0	3	3	3	3	3	3	3	3	1
2023	3	3	3	3	3	3	3	3	3	3	3	0
2024	2	2	2	2	2	2	2	2	2	2	2	3

30/04/2022
30/04/2022
30/04/2022
31/05/2022
31/05/2022
31/05/2022
etc

Is this possible?

Thanks
Max

 

[JEC]

Assuming your data starts in A1 (2022 in A2, Jan in B1)

This puts the list in column P

Sub jecc()
 Dim sq(), ar, j As Long, jj As Long, jjj As Long, x As Long
 ar = Cells(2, 1).CurrentRegion
 
 For j = 2 To UBound(ar)
   For jj = 2 To UBound(ar, 2)
     For jjj = 1 To ar(j, jj)
       ReDim Preserve sq(x)
        sq(x) = DateSerial(ar(j, 1), Month("1-" & ar(1, jj)) + 1, 0)
        x = x + 1
     Next
   Next
 Next
     
 Cells(2, 16).Resize(x) = Application.Transpose(sq)
End Sub

 

[maximus510]

Works to perfection thank you so much!!!

 

[JEC]

You're welcome!