# Sum cells adjacent to duplicates and create only one row...

#### Francois Bekker

##### New Member
Please see a extract from my data set.

I would like to sum the cells adjacent to the left of the duplicates in the Service_Week Column. This means I would like to, in this example, sum (7+3) in Days_Active column and (0+0) in Wild_Moth_Count column and (9+3) in Sterile_Moth_count column. So my aim it to have one value for each Service_Week. In this case the service date and service day doesn't matter. I only need the to get the total amount of days active, Wild moth count and sterile moth count in each specific week.

Does anyone have a plan to sort out this problem please?

Kind regards

 Trap_ID Service_Date Service_Day Service_Week Days_Active Wild_Moth_Count Sterile_Moth_Count Ajlp 1 2010/01/20 Wednesday 3 7 0 5 Ajlp 1 2010/02/01 Monday 5 12 0 11 Ajlp 1 2010/02/08 Monday 6 7 0 9 Ajlp 1 2010/02/11 Thursday 6 3 0 3 Ajlp 1 2010/02/17 Wednesday 7 6 0 47 Ajlp 1 2010/02/24 Wednesday 8 7 0 12 Ajlp 1 2010/03/03 Wednesday 9 7 0 85

<tbody>
</tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try:-
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG28May49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Dn.Value & Dn.Offset(, 3).Value
[COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 4 To 6
.Item(Txt).Offset(, n).Value = _
.Item(Txt).Offset(, n).Value + Dn.Offset(, n).Value
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Dn
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Replies
14
Views
395
Replies
6
Views
434
Replies
0
Views
190
Replies
5
Views
136
Replies
5
Views
245

1,196,047
Messages
6,013,078
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back