Sub transform_data()
'
'
'
Dim td_dates, td_emp_ids, td_shifts, td_data_rows, td_data_cols
Dim td_count1, td_count2, td_count3, td_last_shift, td_last_date, td_this_shift, td_this_date
'
Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
td_data_rows = Selection.Rows.Count
Range("A1").Select
td_data_cols = Range(Selection, Selection.End(xlToRight)).Select
td_data_cols = Selection.Columns.Count
td_dates = Range("a1").Offset(0, 3).Resize(1, td_data_cols - 3).Value
td_emp_ids = Range("a1").Offset(1, 1).Resize(td_data_rows - 1, 1).Value
td_shifts = Range("a1").Offset(1, 3).Resize(td_data_rows - 1, td_data_cols - 3).Value
Sheets("Sheet1").Select

td_count3 = 1
For td_count1 = 1 To td_data_rows - 1
td_count3 = td_count3 + 1
td_last_shift = td_shifts(td_count1, 1)
td_last_date = td_dates(1, 1)
Cells(td_count3, 1) = td_emp_ids(td_count1, 1)
Cells(td_count3, 2) = td_last_shift
Cells(td_count3, 3) = td_last_date
For td_count2 = 2 To td_data_cols - 3
td_this_shift = td_shifts(td_count1, td_count2)
td_this_date = td_dates(1, td_count2)
If (td_this_shift = td_last_shift) Then
td_last_date = td_this_date
Else
Cells(td_count3, 4) = td_last_date
td_count3 = td_count3 + 1
Cells(td_count3, 1) = td_emp_ids(td_count1, 1)
Cells(td_count3, 2) = td_this_shift
Cells(td_count3, 3) = td_this_date
td_last_shift = td_this_shift
td_last_date = td_this_date
End If
Next
Cells(td_count3, 4) = td_last_date
Next
End Sub