Excel Help

Copper Contributor

I have an excel file with emp id, name, designation, department, date, ot hours and normal hours as columns.
Here are some sample data
111,John,Manager,HR,01/01/2024,0,9
111,John,Manager,HR,01/03/2024,3,0
111,John,Manager,HR,01/04/2024,2,9
222,Rogr,Supervr,MM,01/01/2024,0,9
222,Rogr,Supervr,MM,01/02/2024,0,9
333,Sand,PSWABCs,NG,01/15/2024,0,9
333,Sand,PSWABCs,NG,01/16/2024,0,9
333,Sand,PSWABCs,NG,01/17/2024,0,9
333,Sand,PSWABCs,NG,01/19/2024,0,9
333,Sand,PSWABCs,NG,01/25/2024,4,9

My requirement is show emp id, name, designation, department, type and convert the date into column and show the ot and normal hours. Type column should show the hour type (OT or Normal)
If the employee got both ot and normal, there should be 2 rows. Row 1 normal hours and Row 2 OT hours.
Also, if for the month a date is missing, I still need to see that as a header column and show 0 for both OT and normal.

With the above sample data, I am looking for the following results for employee 111
111,John,Manager,HR,01/01/2024,OT,0
111,John,Manager,HR,01/01/2024,Normal,9
111,John,Manager,HR,01/03/2024,OT,3
111,John,Manager,HR,01/03/2024,Normal,0
111,John,Manager,HR,01/04/2024,OT,2
111,John,Manager,HR,01/04/2024,Normal,9

1 Reply

@don075 

Correction.
For the result, i need 3 rows for each employee (one for OT and the other Normal). Teh result should look like the following for the sample data included.

don075_0-1716944362447.png