Merge "like rows" and sum the "Total Hours" value

[Clooney]

So I have this data set that I use weekly for my employees. No idea why this is happening but it is now splitting the "Regular" Pay Code column up and its wreaking havoc with a company wide spreadsheet.

What I want to do is merge the employees who have these 2 "Regular" Pay Code rows merged into one row and have the Actual Total hours column summed but leave the rest of the list intact. Hopefully my image shows better at what I am trying to explain.

 

[Clooney]

Try this :

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Raw Data								Result
2	Employee Full Name	Employee ID	Position	Custom Field	Pay Code	Shift	Actual Total Hours (Include Corrections)		Employee Full Name	Employee ID	Position	Custom Field	Pay Code	Shift	Actual Total Hours (Include Corrections)
3	Ashley, Adam M	2645			Regular	130-1-Lebanon $0.0	32		Ashley, Adam M	2645	0	0	Regular	130-1-Lebanon $0.0	40.00
4	Ashley, Adam M	2645			Regular	Semi-Mon	8		Ashley, Elizabeth	17933	0	0	Overtime	130-1-Lebanon $0.0	3.17
5	Ashley, Elizabeth	17933			Overtime	130-1-Lebanon $0.0	3.17		Ashley, Elizabeth	17933	0	0	Personal U	130-1-Lebanon $0.0	8.00
6	Ashley, Elizabeth	17933			Personal U	130-1-Lebanon $0.0	8		Ashley, Elizabeth	17933	0	0	Regular	130-1-Lebanon $0.0	32.00
7	Ashley, Elizabeth	17933			Regular	130-1-Lebanon $0.0	32		Ayers, Larry D	21384	WAREASSOC	0	Termination	1	-
8	Ayers, Larry D	21384	WAREASSOC		Termination	1	0		Baker, Ann M	18933	0	0	Overtime	130-3-Lebanon $1.0	8.08
9	Baker, Ann M	18933			Overtime	130-3-Lebanon $1.0	8.08		Baker, Ann M	18933	0	0	Regular	130-3-Lebanon $1.0	40.00
10	Baker, Ann M	18933			Regular	130-3-Lebanon $1.0	8		Baker, Jerry L	10411	0	0	Regular	130-1-Lebanon $0.0	40.00
11	Baker, Ann M	18933			Regular	130-3-Lebanon $1.0	22.97		Bills, Leon	19418	0	0	Overtime	130-1-Lebanon $0.0	10.97
12	Baker, Ann M	18933			Regular	1	9.03		Bills, Leon	19418	0	0	Regular	130-1-Lebanon $0.0	40.00
13	Baker, Jerry L	10411			Regular	130-1-Lebanon $0.0	40		Boula, Jean-Claude E	10438	0	0	Overtime	130-1-Lebanon $0.0	10.43
14	Bills, Leon	19418			Overtime	130-1-Lebanon $0.0	10.97		Boula, Jean-Claude E	10438	0	0	Regular	130-1-Lebanon $0.0	40.00
15	Bills, Leon	19418			Regular	130-1-Lebanon $0.0	40		Boyer, Homer W	21538	0	0	Overtime	130-2-Lebanon $1.0	3.47
16	Boula, Jean-Claude E	10438			Overtime	130-1-Lebanon $0.0	10.43		Boyer, Homer W	21538	0	0	Regular	130-2-Lebanon $1.0	40.00
17	Boula, Jean-Claude E	10438			Regular	130-1-Lebanon $0.0	40		Broach, Allison R	21361	0	0	Overtime	130-1-Lebanon $0.0	15.63
18	Boyer, Homer W	21538			Overtime	130-2-Lebanon $1.0	3.47		Broach, Allison R	21361	0	0	Regular	130-1-Lebanon $0.0	40.00
19	Boyer, Homer W	21538			Regular	130-2-Lebanon $1.0	40
20	Broach, Allison R	21361			Overtime	130-1-Lebanon $0.0	15.63
21	Broach, Allison R	21361			Regular	130-1-Lebanon $0.0	40
Sheet1

Cell Formulas
Range	Formula
I2:O2	I2	=A2:G2
I3:M18	I3	=UNIQUE(A3:E21)
N3:N19	N3	=IF(I3<>"",XLOOKUP(I3&"!"&M3,A$3:A$21&"!"&E$3:E$21,F$3:F$21),"")
O3:O19	O3	=IF(I3<>"",SUMIFS(G$3:G$21,A$3:A$21,I3,E$3:E$21,M3),"")
Dynamic array formulas.

This works perfect once you delete all the values that appear in column C.


Thank you very much!

 

[Sanjeev1976]

Thank you for your feedback