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

[TABLE="width: 500"]
<tbody>[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]bob marley[/TD]
[TD]238281[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]mark john[/TD]
[TD]383282[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

which I need to turn into

[TABLE="width: 500"]
<tbody>[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]bob marley[/TD]
[TD]238281[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]mark john[/TD]
[TD]383282[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[oldbrewer]

[TABLE="width: 520"]
<colgroup><col><col span="4"><col><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]idnum[/TD]
[TD]hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD="align: right"]128128[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD="align: right"]128128[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD="align: right"]128128[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob marley[/TD]
[TD="align: right"]238281[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum of hours[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD="align: right"]91919[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD="align: right"]91919[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]bob marley[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mark john[/TD]
[TD="align: right"]383282[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]jane doe[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]john doe[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mark john[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]86[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]a very simple pivot table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[azumi]

find this link

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

hope this works

 

[isaackoh8393]

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

 

[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

[TABLE="width: 500"]
<tbody>[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]bob marley[/TD]
[TD]238281[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]mark john[/TD]
[TD]383282[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

which I need to turn into

[TABLE="width: 500"]
<tbody>[TR]
[TD]john doe[/TD]
[TD]128128[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]bob marley[/TD]
[TD]238281[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]jane doe[/TD]
[TD]91919[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]mark john[/TD]
[TD]383282[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

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!)