VBA - Consolidate data and Sum mulitiple ranges

LucyJeanie

New Member
Joined
Feb 14, 2019
Messages
1
Hi is there anyone who can help. I'm working on a project and I need a VBA Code to consolidate multiple rows based on a single column entry, then adding together the other columns. At the moment the data looks like this:

Financial Year & PeriodIncident Start DateIncident NumberPeriod CountIncident DescriptionCategoryWMTRDF
2018/19_P0320/05/2018 10:30207051A87ABC1512
2018/19_P0420/05/2018 10:30207051A87ABC4652
<colgroup><col width="170" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6217;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="165" style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <tbody> </tbody>

and I need it to look like this:

Financial Year & PeriodIncident Start DateIncident NumberPeriod CountIncident DescriptionCategoryWMTRDF
2018/19_P0320/05/2018 10:3020705
2A87ABC6164
<colgroup><col width="170" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6217;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="165" style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <tbody> </tbody>

I need Period Count, RDF and WMT to be a total sum.

I've tried few variations on a code I have but it's not working properly.

PLEASE HELP!
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG14Feb09
[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] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Offset(, 2).Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Offset(, 2).Value, Dn
[COLOR="Navy"]Else[/COLOR]
   [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
   .Item(Dn.Offset(, 2).Value).Offset(, 3).Value = _
   .Item(Dn.Offset(, 2).Value).Offset(, 3).Value + Dn.Offset(, 3).Value
   .Item(Dn.Offset(, 2).Value).Offset(, 6).Value = _
   .Item(Dn.Offset(, 2).Value).Offset(, 6).Value + Dn.Offset(, 6).Value
   .Item(Dn.Offset(, 2).Value).Offset(, 7).Value = _
   .Item(Dn.Offset(, 2).Value).Offset(, 7).Value + Dn.Offset(, 7).Value
[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] With
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Forum statistics

Threads
1,081,545
Messages
5,359,450
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top