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.

 

[SanjayGMusafir]

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.

You didn't mention the excel version and platform you are using - That shall effect how your intended result can be achieved.

You can update same in your profile or at least mention it here to help you better.

 

[Clooney]

You didn't mention the excel version and platform you are using - That shall effect how your intended result can be achieved.

You can update same in your profile or at least mention it here to help you better.

Sorry, Office 365 on PC. I havent been online since this site updated so it looks like all of my personal info was wiped. I will update it again.

 

[SanjayGMusafir]

Sorry, Office 365 on PC. I havent been online since this site updated so it looks like all of my personal info was wiped. I will update it again.

then it's really easy

use UNIQUE function to get EmloyeeFullName in cell (example M1)
Pull rest of the data using XLOOKUP Function referencing Cell (M1#) # helps to propagate formula across spill

for Actual total use SUMIFS Function

Hope this helps
Best wishes

 

[SanjayGMusafir]

then it's really easy

use UNIQUE function to get EmloyeeFullName in cell (example M1)
Pull rest of the data using XLOOKUP Function referencing Cell (M1#) # helps to propagate formula across spill

for Actual total use SUMIFS Function

Hope this helps
Best wishes

There is a glitch in it - I just saw that in your data Employees have different types of Paycode

In that case you can have such a structure

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1	Raw Data					Result
2	Name	ID	PayCode	Hours		Name	ID	Regular	Other	Overtime		PayCode
3	Employee 01	XYZ	Regular	34		Employee 01	XYZ	73	20	10		Regular
4	Employee 02	LMN	Regular	24		Employee 02	LMN	24	0	0		Other
5	Employee 03	QRST	Other	8		Employee 03	QRST	0	8	0		Overtime
6	Employee 04	XYZR	Other	38		Employee 04	XYZR	0	38	0
7	Employee 05	ABCD	Other	35		Employee 05	ABCD	0	35	0
8	Employee 06	EFGH	Other	34		Employee 06	EFGH	0	34	0
9	Employee 07	TRY	Regular	17		Employee 07	TRY	43	10	15
10	Employee 07	TRY	Other	10		Employee 08	HI	0	37	0
11	Employee 08	HI	Other	37		Employee 09	GIVE	0	22	0
12	Employee 09	GIVE	Other	22		Employee 10	ANOTHER	0	21	0
13	Employee 10	ANOTHER	Other	21		Employee 11	TRY	0	22	0
14	Employee 11	TRY	Other	22
15	Employee 01	XYZ	Regular	39
16	Employee 07	TRY	Regular	26
17	Employee 01	XYZ	Overtime	10
18	Employee 07	TRY	Overtime	15
19	Employee 01	XYZ	Other	20
Example

Cell Formulas
Range	Formula
H2:J2	H2	=TRANSPOSE(L3#)
F3:F13	F3	=UNIQUE(A3:A16)
G3:G13	G3	=XLOOKUP($F3#,A3:A16,B3:B16)
H3:J13	H3	=SUMIFS($D$3:$D$19,$A$3:$A$19,$F3#,$C$3:$C$19,H$2)
L3:L5	L3	=UNIQUE(C3:C18)
D3:D9,D11:D16	D3	=RANDBETWEEN(1,40)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D16	Expression	=$C3="Regular"	text	NO

 

[SanjayGMusafir]

Sorry, Office 365 on PC. I havent been online since this site updated so it looks like all of my personal info was wiped. I will update it again.

Check This

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Raw Data					Result
2	Name	ID	PayCode	Hours		Name	ID	Regular	Other	Overtime
3	Employee 01	XYZ	Regular	34		Employee 01	XYZ	73	20	10
4	Employee 02	LMN	Regular	24		Employee 02	LMN	24	0	0
5	Employee 03	QRST	Other	8		Employee 03	QRST	0	8	0
6	Employee 04	XYZR	Other	38		Employee 04	XYZR	0	38	0
7	Employee 05	ABCD	Other	35		Employee 05	ABCD	0	35	0
8	Employee 06	EFGH	Other	34		Employee 06	EFGH	0	34	0
9	Employee 07	TEST	Regular	17		Employee 07	TEST	43	10	15
10	Employee 07	TEST	Other	10		Employee 08	HI	0	37	0
11	Employee 08	HI	Other	37		Employee 09	GIVE	0	22	0
12	Employee 09	GIVE	Other	22		Employee 10	ANOTHER	0	21	0
13	Employee 10	ANOTHER	Other	21		Employee 11	TRY	0	22	0
14	Employee 11	TRY	Other	22
15	Employee 01	XYZ	Regular	39
16	Employee 07	TEST	Regular	26
17	Employee 01	XYZ	Overtime	10
18	Employee 07	TEST	Overtime	15
19	Employee 01	XYZ	Other	20
Example

Cell Formulas
Range	Formula
H2:J2	H2	=TRANSPOSE(UNIQUE(C3:C18))
F3:F13	F3	=UNIQUE(A3:A16)
G3:G13	G3	=XLOOKUP($F3#,A3:A16,B3:B16)
H3:J13	H3	=SUMIFS($D$3:$D$19,$A$3:$A$19,$F3#,$C$3:$C$19,H$2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D16	Expression	=$C3="Regular"	text	NO

 

[Clooney]

Check This

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Raw Data					Result
2	Name	ID	PayCode	Hours		Name	ID	Regular	Other	Overtime
3	Employee 01	XYZ	Regular	34		Employee 01	XYZ	73	20	10
4	Employee 02	LMN	Regular	24		Employee 02	LMN	24	0	0
5	Employee 03	QRST	Other	8		Employee 03	QRST	0	8	0
6	Employee 04	XYZR	Other	38		Employee 04	XYZR	0	38	0
7	Employee 05	ABCD	Other	35		Employee 05	ABCD	0	35	0
8	Employee 06	EFGH	Other	34		Employee 06	EFGH	0	34	0
9	Employee 07	TEST	Regular	17		Employee 07	TEST	43	10	15
10	Employee 07	TEST	Other	10		Employee 08	HI	0	37	0
11	Employee 08	HI	Other	37		Employee 09	GIVE	0	22	0
12	Employee 09	GIVE	Other	22		Employee 10	ANOTHER	0	21	0
13	Employee 10	ANOTHER	Other	21		Employee 11	TRY	0	22	0
14	Employee 11	TRY	Other	22
15	Employee 01	XYZ	Regular	39
16	Employee 07	TEST	Regular	26
17	Employee 01	XYZ	Overtime	10
18	Employee 07	TEST	Overtime	15
19	Employee 01	XYZ	Other	20
Example

Cell Formulas
Range	Formula
H2:J2	H2	=TRANSPOSE(UNIQUE(C3:C18))
F3:F13	F3	=UNIQUE(A3:A16)
G3:G13	G3	=XLOOKUP($F3#,A3:A16,B3:B16)
H3:J13	H3	=SUMIFS($D$3:$D$19,$A$3:$A$19,$F3#,$C$3:$C$19,H$2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D16	Expression	=$C3="Regular"	text	NO

That's definitely a start. I can actually use this as a set of helper columns. I will dig into this in the morning.

Thank you so much. I will reply with my findings.

 

[Clooney]

That's a wonderful solution, if I could change our corporate sheet to this, it would save a huge headache but unfortunately they keep it locked down and there is a lot of red tape that has to happen in order to make the change.

So with everything basically transposed I need the data transposed back into a "Paycode" column just using the "Regular" and "Overtime" hours to where if an employee has both Regular and Overtime hours their name and ID will appear twice in the list.

2W Payroll 07312022.xls
	A	B	C	D	E	F	G
10	Employee Full Name	Employee ID	Position	Custom Field	Pay Code	Shift	Actual Total Hours (Include Corrections)
11	Ashley, Adam M	2645			Regular	130-1-Lebanon $0.0	40
12	Ashley, Elizabeth	17933			Overtime	130-1-Lebanon $0.0	3.17
13	Ashley, Elizabeth	17933			Regular	130-1-Lebanon $0.0	32
14	Baker, Ann M	18933			Overtime	130-3-Lebanon $1.0	8.08
15	Baker, Ann M	18933			Regular	130-3-Lebanon $1.0	30
16	Baker, Jerry L	10411			Regular	130-1-Lebanon $0.0	40
17	Bills, Leon	19418			Overtime	130-1-Lebanon $0.0	10.97
18	Bills, Leon	19418			Regular	130-1-Lebanon $0.0	40
19	Boula, Jean-Claude E	10438			Overtime	130-1-Lebanon $0.0	10.43
20	Boula, Jean-Claude E	10438			Regular	130-1-Lebanon $0.0	40
21	Boyer, Homer W	21538			Overtime	130-2-Lebanon $1.0	3.47
22	Boyer, Homer W	21538			Regular	130-2-Lebanon $1.0	40
23	Broach, Allison R	21361			Overtime	130-1-Lebanon $0.0	15.63
24	Broach, Allison R	21361			Regular	130-1-Lebanon $0.0	40
Sheet1

 

[Sanjeev1976]

Please share the raw data using XL2BB tool

 

[Sanjeev1976]

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.