Hello all,
I would like to transpose some vertical data (timestamps) in worksheet 3 horizontally to worksheet 2.
Steps
1. If you can't find the date from worksheet 2 column A in worksheet 3 column F, nothing should happen.
2. If you can find the date, count the amount of times you find this date and copy the corresponding timestamps (to this date) to worksheet 2.
E.g. 5/07/2017 has 2 timestamps in worksheet 3, thus worksheet 2 should only show a value in column Time1 and Time2.
I don't really know how to loop through this or work with select case or so...
Here below my flawed attempt, but the offset is a bit off and it doesn't copy the right amount of timestamps.
lastRow = ws3.Cells(Rows.Count, "F").End(xlUp).Row
Set r = ws3.Range("F2:F" & lastRow)
i = 2
Do While ws2.Cells(i, 1).Value <> ""
For x = 1 To Application.WorksheetFunction.CountIf(ws3.Range("F:F"), ws2.Cells(i, 1))
If r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) Is Nothing Then
ElseIf r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) >= 1 Then
ws2.Cells(i, 6).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x, 1)
ws2.Cells(i, 11).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 1, 1)
ws2.Cells(i, 16).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 2, 1)
ws2.Cells(i, 21).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 3, 1)
ws2.Cells(i, 26).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 4, 1)
ws2.Cells(i, 31).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 5, 1)
End If
Next x
i = i + 1
Loop
Expected result:
Capture.JPG
Bookmarks