Summing values from duplicate rows into the original then deleting duplicates

[ThePangloss]

I'm looking for a way to add up the values of duplicate rows underneath the original then delete the duplicate afterwards, whether this is through VBA or just formulas. I can't really think of any way other than using the IF function such as

=IF((A2=A3)*(B2=B3),"",SUM(C$<wbr style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; line-height: 16.8999996185303px; white-space: pre-wrap;">2:C2)-SUM(D$1:D1))

but this only works for rows that have only one duplicate row. Some of my data has three to five duplicate rows with values that I need to sum up in the main one. The rows I have are last name, first name, id number, then hours worked. Some people worked different jobs at different periods so they come up multiple times. I need to add up their total hours.

An example looks like this

john doe	128128	10
john doe	128128	21
john doe	128128	8
bob marley	238281	10
jane doe	91919	7
jane doe	91919	10
mark john	383282	20

<tbody>
</tbody>

which I need to turn into

john doe	128128	39
bob marley	238281	10
jane doe	91919	17
mark john	383282	20

<tbody>
</tbody>

I'd appreciate any advice on this. Thanks in advance.

 

[oldbrewer]

name	idnum	hours
john doe	128128	10
john doe	128128	21
john doe	128128	8
bob marley	238281	10			Sum of hours
jane doe	91919	7			name	Total
jane doe	91919	10			bob marley	10
mark john	383282	20			jane doe	17
					john doe	39
					mark john	20
					Grand Total	86
			a very simple pivot table

<colgroup><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>

 

[azumi]

find this link

https://simpan.ugm.ac.id/public.php?service=files&t=91e97fd76bb54eeb5ead72d8ee0fa57a

hope this works

 

[isaackoh8393]

				=COUNTIF($A1:A$2,A1)	=IF(G1=1,VALUE("1."&COUNTIF($G1:G$2,1)))
john doe	128128	10		1	1.1
john doe	128128	21		2	FALSE
john doe	128128	8		3	FALSE
bob marley	238281	10		1	1.2
jane doe	91919	7		1	1.3
jane doe	91919	10		2	FALSE
mark john	383282	20		1	1.4
	=INDEX(A1:A7,MATCH(A12,H1:H7,0))	=SUMIF($A$2:$A$8,$B12,$C$2:$C$8)
1.1	john doe	39
1.2	bob marley	10
1.3	jane doe	17
1.4	mark john	20
1.5	#N/A	0
1.6	#N/A	0

<tbody>
</tbody>

 

[isaackoh8393]

I'm looking for a way to add up the values of duplicate rows underneath the original then delete the duplicate afterwards, whether this is through VBA or just formulas. I can't really think of any way other than using the IF function such as

=IF((A2=A3)*(B2=B3),"",SUM(C$<wbr style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; line-height: 16.8999996185303px; white-space: pre-wrap;">2:C2)-SUM(D$1:D1))

but this only works for rows that have only one duplicate row. Some of my data has three to five duplicate rows with values that I need to sum up in the main one. The rows I have are last name, first name, id number, then hours worked. Some people worked different jobs at different periods so they come up multiple times. I need to add up their total hours.

An example looks like this

john doe	128128	10
john doe	128128	21
john doe	128128	8
bob marley	238281	10
jane doe	91919	7
jane doe	91919	10
mark john	383282	20

<tbody>
</tbody>

which I need to turn into

john doe	128128	39
bob marley	238281	10
jane doe	91919	17
mark john	383282	20

<tbody>
</tbody>

I'd appreciate any advice on this. Thanks in advance.

Pangloss, please check the formula i had sent. You need to define the unique name 1st. Just may sum out the value.

 

[ThePangloss]

Thanks a lot everyone! I went with Azumi's formula as it didn't involve pivot tables or secondary indexes (they're still extremely helpful ideas though!)