Make a selection based on a date

[SamyB]

Hello Everyone,

I am trying to find a macro solution to the following formatting issue.

I have 2 columns in sheet1 (see attached picture); the date column and Value 1 column. I have multiple values for the same date for the whole year and I want to change the format to be as per sheet 2 (attached picture)
Basically, I want all the values of the same date to be copied and pasted in a transposed manner in the correct row on sheet 2. The selection of the values in sheet 1 should be based on the date. it would be great to have a loop that goes through the dates by increasing one day at a time.

in my own programing language, it would be:

Select range in column B where the range in column A date is equal to a specific date
copy the selection
paste transposed in sheet2
go to the next date and repeat

Thank you in advance
Regards
SamyB

 

[DanteAmor]

Welcome to the MrExcel Message Board!

Try this:

Sub TransposeValues()
  Dim a As Variant, b As Variant
  Dim dic As Object
  Dim fec As Double
  Dim i As Long, j As Long, k As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("B" & Rows.Count).End(3)).Value2
  b = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("C" & Rows.Count).End(3)).Value2
  ReDim c(1 To UBound(b, 1), 1 To 1000)
  
  For i = 1 To UBound(b, 1)
    fec = CDate(b(i, 3) & "/" & b(i, 2) & "/" & b(1, 1))
    dic(fec) = i & "|0"
  Next
  
  For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 1)) Then
      j = Split(dic(a(i, 1)), "|")(0)
      k = Split(dic(a(i, 1)), "|")(1) + 1
      c(j, k) = a(i, 2)
      dic(a(i, 1)) = j & "|" & k
    End If
  Next
  
  Sheets("Sheet2").Range("D2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub