data manipulation

[Thilak]

Dear Friends.

I have a Excel workbook with two sheets. I need a easy program/Formula to transfer the data From the Second Tab sheet (Department Template) to the First Sheet (Cosec Template).

In Department Template:

SL NO EMP-ID NAME 21/12/15 22/12/15 23/12/15 24/12/15 25/12/15 26/12/15
1 270 GANGADHARAPPA GS CL CL GS GH GS
2 276 RAMACHANDRA REDDY GS GS GS GS GH GS
3 274 RAVI K.K NS NS NS NS NS NS
4 1178 ` GS GS GS M1 GH CL
5 2353 SANDEEP KUMAR S3 M1 C/O C/O GH CL

I need a program to take this data aand put it in another sheet called COSEC Template & The desired result should be like as :


UserID Shift ID/Day Marking FROM Date TO Date


270 GS 21/12/2016 21/12/2016
270 CL 22/12/2016 23/12/2016

270 GS 24/12/2016 24/12/2016

270 GH 25/12/2016 26/12/2016
270 W/O 27/12/2016 27/12/2016
270 GS 28/12/2016 02/01/2016

I required the programming to fill up tis data for the particular employee till the end of date 20th (month end)as per sheet2. Then start the next employee starts from 21st to 20th, then next and on until ends. Kindly Note, all in sheet 1 (COSEC Template) should be in Formulas. No manual entries should be there. Need to transfer data from sheet2 to sheet 1 in that format.

Please provide your valuable solutions. Thank u so much for the help in advance.

Regards.
Thilak N

[ld_kt12]

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