Adding together cell values if Duplicate value in another cell

[cwills]

Hi

I have a sheet of data which is produced monthly, listing the start, finish and total time taken for a specific task. This is split into two rows of data as the task has 2 elements to it for completion.

What I need is for Column A to be checked and where there are duplicate values, the time in Column H to be added together and the duplicate row removed

Any help would be appreciated, I have tried to use the consolidate function but this isn't removing the duplicate rows correctly

Thank you

 

[ExceLoki]

if you're ok with having a second table to consolidate the information this will work. This is also dependent on the table being sorted by sample reference values (column a).
i like doing things this way to keep my original data incase ever need to go back to it, instead of completely deleting it.
----------------------

Adding together cell values if Duplicate value in another cell.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	sample ref	date	analysis start time	analysis end time	analyst	result	classification	calc time	total time		sample ref	date	total time	analyst	result	classification
2	a	2/1/2022	6:06:52	6:19:20	andrea	no	texture	0:12:28	0:15:59		a	2/1/2022	0:15:59	andrea	no	texture
3	a	2/1/2022	6:22:41	6:26:12	andrea	no	texture	0:03:31			b	2/1/2022	0:14:25	maria	no	texture
4	b	2/1/2022	6:12:43	6:23:33	maria	no	texture	0:10:50	0:14:25		c	2/1/2022	0:15:32	andrea	no	texture
5	b	2/1/2022	6:23:40	6:27:15	maria	no	texture	0:03:35			d	2/1/2022	0:15:04	maria	no	texture
6	c	2/1/2022	6:26:17	6:33:58	andrea	no	texture	0:07:41	0:15:32		e	2/1/2022	0:15:07	andrea	no	texture
7	c	2/1/2022	6:34:09	6:42:00	andrea	no	texture	0:07:51			f	2/1/2022	0:14:57	maria	no	texture
8	d	2/1/2022	6:27:48	6:34:53	maria	no	texture	0:07:05	0:15:04		g	2/1/2022	0:15:18	andrea	no	insulating
9	d	2/1/2022	6:35:40	6:43:39	maria	no	texture	0:07:59			h	2/1/2022	0:15:02	andrea	no	texture
10	e	2/1/2022	6:42:08	6:51:33	andrea	no	texture	0:09:25	0:15:07		i	2/1/2022	0:04:54	andrea	chrysotile	texture
11	e	2/1/2022	6:51:35	6:57:17	andrea	no	texture	0:05:42			j	2/1/2022	0:14:40	maria	no	texture
12	f	2/1/2022	6:44:28	6:52:37	maria	no	texture	0:08:09	0:14:57
13	f	2/1/2022	6:53:18	7:00:06	maria	no	texture	0:06:48
14	g	2/1/2022	6:58:20	7:07:24	andrea	no	insulating	0:09:04	0:15:18
15	g	2/1/2022	7:07:28	7:13:42	andrea	no	insulating	0:06:14
16	h	2/1/2022	7:00:34	7:13:31	andrea	no	texture	0:12:57	0:15:02
17	h	2/1/2022	7:13:55	7:16:00	maria	no	texture	0:02:05
18	i	2/1/2022	7:15:35	7:20:29	andrea	chrysotile	texture	0:04:54	0:04:54
19	j	2/1/2022	7:16:21	7:28:29	maria	no	texture	0:12:08	0:14:40
20	j	2/1/2022	7:28:59	7:31:31	maria	no	texture	0:02:32
Sheet1

Cell Formulas
Range	Formula
H2:H20	H2	=D2-C2
I2:I20	I2	=IF(A2=A1,"",SUMIF(A:H,A:A,H:H))
L2:L11	L2	=VLOOKUP(K2,A:I,2,FALSE)
M2:M11	M2	=VLOOKUP(K2,A:I,9,FALSE)
N2:N11	N2	=VLOOKUP(K2,A:I,5,FALSE)
O2:O11	O2	=VLOOKUP(K2,A:I,6,FALSE)
P2:P11	P2	=VLOOKUP(K2,A:I,7,FALSE)

 

[cwills]

Thank you ExcelLoki,

Quick question, how are you populating column K with the sample ref's excluding any duplicates?

Everything else works perfectly and will cut down a lot of my time going through this sheet each month.

 

[bebo021999]

SO_CHI_TIET_BAN_HANG.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	AM005296	01-Feb	06:06	06:19	blah	blah	blah	00:12			AM005296	01-Feb	blah	blah	blah	00:16
3	AM005296	01-Feb	06:22	06:26	blah	blah	blah	00:04			AM005297	01-Feb	blah	blah	blah	00:11
4	AM005297	01-Feb	06:12	06:23	blah	blah	blah	00:11			AM005298	01-Feb	blah	blah	blah	00:16
5	AM005298	01-Feb	06:23	06:27	blah	blah	blah	00:04			AM005299	01-Feb	blah	blah	blah	00:04
6	AM005298	01-Feb	06:07	06:20	blah	blah	blah	00:12			AM005300	01-Feb	blah	blah	blah	00:15
7	AM005299	01-Feb	06:23	06:27	blah	blah	blah	00:04			AM005301	01-Feb	blah	blah	blah	00:12
8	AM005300	01-Feb	06:13	06:24	blah	blah	blah	00:11			AM005302	01-Feb	blah	blah	blah	00:15
9	AM005300	01-Feb	06:24	06:28	blah	blah	blah	00:04			AM005303	01-Feb	blah	blah	blah	00:04
10	AM005301	01-Feb	06:08	06:21	blah	blah	blah	00:12			AM005304	01-Feb	blah	blah	blah	00:12
11	AM005302	01-Feb	06:24	06:28	blah	blah	blah	00:04			AM005305	01-Feb	blah	blah	blah	00:15
12	AM005302	01-Feb	06:14	06:25	blah	blah	blah	00:11
13	AM005303	01-Feb	06:25	06:29	blah	blah	blah	00:04
14	AM005304	01-Feb	06:09	06:22	blah	blah	blah	00:12
15	AM005305	01-Feb	06:25	06:29	blah	blah	blah	00:04
16	AM005305	01-Feb	06:15	06:26	blah	blah	blah	00:11
Sheet1

Cell Formulas
Range	Formula
K2:K15	K2	=IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($K$1:K1,$A$2:$A$16),),0)),"")
L2:L15	L2	=IF($K2="","",VLOOKUP($K2,$A$2:$H$16,2,0))
M2:M15	M2	=IF($K2="","",VLOOKUP($K2,$A$2:$H$16,5,0))
N2:N15	N2	=IF($K2="","",VLOOKUP($K2,$A$2:$H$16,6,0))
O2:O15	O2	=IF($K2="","",VLOOKUP($K2,$A$2:$H$16,7,0))
P2:P15	P2	=IF($K2="","",SUMIF($A$2:$A$16,K2,$H$2:$H$16))
A2	A2	="AM00"&ROW(5296:5296)
A3,A12	A3	=A2
H2:H16	H2	=D2-C2

 

[ExceLoki]

Thank you ExcelLoki,

Quick question, how are you populating column K with the sample ref's excluding any duplicates?

Everything else works perfectly and will cut down a lot of my time going through this sheet each month.

i had just manually added them, but using what @bebo021999 put for that this should be good
------------------

Adding together cell values if Duplicate value in another cell.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	sample ref	date	analysis start time	analysis end time	analyst	result	classification	calc time	total time		sample ref	date	total time	analyst	result	classification
2	a	2/1/2022	6:06:52	6:19:20	andrea	no	texture	0:12:28	0:15:59		a	2/1/2022	0:15:59	andrea	no	texture
3	a	2/1/2022	6:22:41	6:26:12	andrea	no	texture	0:03:31			b	2/1/2022	0:14:25	maria	no	texture
4	b	2/1/2022	6:12:43	6:23:33	maria	no	texture	0:10:50	0:14:25		c	2/1/2022	0:15:32	andrea	no	texture
5	b	2/1/2022	6:23:40	6:27:15	maria	no	texture	0:03:35			d	2/1/2022	0:15:04	maria	no	texture
6	c	2/1/2022	6:26:17	6:33:58	andrea	no	texture	0:07:41	0:15:32		e	2/1/2022	0:15:07	andrea	no	texture
7	c	2/1/2022	6:34:09	6:42:00	andrea	no	texture	0:07:51			f	2/1/2022	0:14:57	maria	no	texture
8	d	2/1/2022	6:27:48	6:34:53	maria	no	texture	0:07:05	0:15:04		g	2/1/2022	0:15:18	andrea	no	insulating
9	d	2/1/2022	6:35:40	6:43:39	maria	no	texture	0:07:59			h	2/1/2022	0:15:02	andrea	no	texture
10	e	2/1/2022	6:42:08	6:51:33	andrea	no	texture	0:09:25	0:15:07		i	2/1/2022	0:04:54	andrea	chrysotile	texture
11	e	2/1/2022	6:51:35	6:57:17	andrea	no	texture	0:05:42			j	2/1/2022	0:14:40	maria	no	texture
12	f	2/1/2022	6:44:28	6:52:37	maria	no	texture	0:08:09	0:14:57
13	f	2/1/2022	6:53:18	7:00:06	maria	no	texture	0:06:48
14	g	2/1/2022	6:58:20	7:07:24	andrea	no	insulating	0:09:04	0:15:18
15	g	2/1/2022	7:07:28	7:13:42	andrea	no	insulating	0:06:14
16	h	2/1/2022	7:00:34	7:13:31	andrea	no	texture	0:12:57	0:15:02
17	h	2/1/2022	7:13:55	7:16:00	maria	no	texture	0:02:05
18	i	2/1/2022	7:15:35	7:20:29	andrea	chrysotile	texture	0:04:54	0:04:54
19	j	2/1/2022	7:16:21	7:28:29	maria	no	texture	0:12:08	0:14:40
20	j	2/1/2022	7:28:59	7:31:31	maria	no	texture	0:02:32
Sheet1

Cell Formulas
Range	Formula
H2:H20	H2	=D2-C2
I2:I20	I2	=IF(A2=A1,"",SUMIF(A:H,A:A,H:H))
K2:K20	K2	=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($K$1:K1,$A$2:$A$20),),0)),"")
L2:L20	L2	=IF(K2="","",VLOOKUP(K2,A:I,2,))
M2:M20	M2	=IF(K2="","",VLOOKUP(K2,A:I,9,FALSE))
N2:N20	N2	=IF(K2="","",VLOOKUP(K2,A:I,5,FALSE))
O2:O20	O2	=IF(K2="","",VLOOKUP(K2,A:I,6,FALSE))
P2:P20	P2	=IF(K2="","",VLOOKUP(K2,A:I,7,FALSE))

 

[cwills]

Thank you both for your help, this will free up a lot of my time in analysing the data and actually getting the results I need