#1
|
|||
|
|||
data manipulation
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 |
#2
|
|||
|
|||
This code may solve your problem
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
A challenging digit by digit manipulation, rotate the digit in range of 0 to 9 | laucn | Excel Programming | 14 | 05-17-2015 12:12 PM |
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 | bobznkazoo | Excel | 2 | 03-27-2014 11:14 AM |
Text manipulation in MACRO | srt | Word VBA | 25 | 03-13-2013 03:25 PM |
Combobox manipulation | vsempoux | Word VBA | 3 | 10-31-2009 08:58 AM |
Mail merge Field Code Manipulation | macjnr | Mail Merge | 0 | 09-10-2009 11:37 AM |