Insert row when value changes, sort the isolated information by date and then by description and sum

S

Simona

New Member
Joined
Aug 9, 2010
Messages
13
[FONT=&quot]Hello!
I need your help to build a macro that can do this manual and boring task for me.
I have a list of information and I need to:
1 – Insert two rows every time the code in the column A changes
2 – Sort every single new interval by date (column B) and then by description (column D)
3 – Sum the values in every single new interval (column C)
Do you think this is possible or is it asking too much????
Thank you in advance for your help!
Simona

I hope you can understand it from here:
It lost all the formatting though....<o:p></o:p>[/FONT]
[FONT=&quot]Before<o:p></o:p>[/FONT]
<table class="MsoNormalTable" style="width: 395.45pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="527"> <tbody><tr style="height: 15.75pt;"> <td style="width: 38.5pt; padding: 0cm; height: 15.75pt;" width="51">
</td> <td style="width: 69.75pt; padding: 0cm; height: 15.75pt;" width="93">
[FONT=&quot]A<o:p></o:p>[/FONT]​
</td> <td style="width: 81.75pt; padding: 0cm; height: 15.75pt;" width="109">
[FONT=&quot]B<o:p></o:p>[/FONT]​
</td> <td style="width: 67.2pt; padding: 0cm; height: 15.75pt;" width="90">
[FONT=&quot]C<o:p></o:p>[/FONT]​
</td> <td style="width: 138.25pt; padding: 0cm; height: 15.75pt;" width="184">
[FONT=&quot]D<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Analysis Code<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Transaction Date<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Base Amount<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Description<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]2<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011101<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]2,047.50<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]BOULANG<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]3<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011101<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]20/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]27.53<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]ANT PATISSERIE<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]4<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]04/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]531.00<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]TANT<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]5<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-1,350.48<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Soft Drink Accrual<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]6<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-14,869.60<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Soft Drinks Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]7<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-20,086.75<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]8<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]18,446.90<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]9<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-2,140.37<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Accrual<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]10<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011202<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]18/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]595.80<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]FRESH DISP<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]11<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011202<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]07/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]86.59<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]ABC FOOD<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;"> [FONT=&quot]After<o:p></o:p>[/FONT]
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0cm; height: 15.75pt;">
</td> <td style="padding: 0cm; height: 15.75pt;">
[FONT=&quot]A<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15.75pt;">
[FONT=&quot]B<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15.75pt;">
[FONT=&quot]C<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15.75pt;">
[FONT=&quot]D<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Analysis Code<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Transaction Date<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Base Amount<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Description<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]2<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011101<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]20/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]27.53<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]ANT PATISSERIE<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]3<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011101<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]2,047.50<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]BOULANG<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]4<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]sum<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]2,075.03<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]5<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]6<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-1,350.48<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Soft Drink Accrual<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]7<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-14,869.60<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Soft Drinks Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]8<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011102<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]04/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]531.00<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]TANT<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]9<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]sum<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-15,689.08<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]10<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]11<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-20,086.75<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]12<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]18,446.90<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Stock<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]13<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011201<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]31/07/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-2,140.37<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]Food Accrual<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]14<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]sum<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]-3,780.22<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]15<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]16<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011202<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]18/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]595.80<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]FRESH DISP<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]17<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]1011202<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]07/08/2010<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]86.59<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]ABC FOOD<o:p></o:p>[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]sum<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
[FONT=&quot]682.39<o:p></o:p>[/FONT]​
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Hi Mick,

when I use the sorting option in excel 2007 I am able sort the information using different conditions at the same time (sort by... then by..then by... etc.).

As my knowledge in VBA is very basic I normally use the macro recorder.
Doing it with my example the code I obtained was:

Code: 
Sub ()

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D5:D7"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B5:B7"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      
  With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("A5:D7")
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
  
End Sub
I imagine the condition "with" is the condition necessary to sort more than one option at the same time. My problem is that I am not able to translate this fixed code into a dynamic one which will be working with all the intervals.

Do you have any idea?

In the mean time thank you for the previous answers! I figured out what I was doing wrong in the question 3, your code is correct!
 
Upvote 0
Hi Mick,

when I use the sorting option in excel 2007 I am able sort the information using different conditions at the same time (sort by... then by..then by... etc.).

As my knowledge in VBA is very basic I normally use the macro recorder.
Doing it with my example the code I obtained was:

Code: 
Sub ()

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D5:D7"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B5:B7"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      
  With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("A5:D7")
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
  
End Sub
I imagine the condition "with" is the condition necessary to sort more than one option at the same time. My problem is that I am not able to translate this fixed code into a dynamic one which will be working with all the intervals.

Do you have any idea?

In the mean time thank you for the previous answers! I figured out what I was doing wrong in the question 3, your code is correct!
 
Upvote 0
MickG, I hope you do not mind.


Simona,

Why not sort the data at the beginning of the macro?


Sample data before the macro:


Excel Workbook
ABCDE
1Analysis CodeTransaction DateBase Amount?????Description
2101110131/08/20102,047.50BOULANG
3101110120/07/201027.53ANT PATISSERIE
410111024/8/2010531TANT
5101110231/07/2010-1,350.48Soft Drink Accrual
6101110231/08/2010-14,869.60Soft Drinks Stock
7101120131/08/2010-20,086.75Food Stock
8101120131/07/201018,446.90Food Stock
9101120131/07/2010-2,140.37Food Accrual
10101120218/08/2010595.8FRESH DISP
1110112027/8/201086.59ABC FOOD
12
13
14
15
16
17
18
19
Sheet1





After the updated macro:


Excel Workbook
ABCDE
1Analysis CodeTransaction DateBase Amount?????Description
2101110120/07/201027.53ANT PATISSERIE
3101110131/08/20102,047.50BOULANG
4Sum2,075.03
5
6101110231/07/2010-1,350.48Soft Drink Accrual
7101110231/08/2010-14,869.60Soft Drinks Stock
810111024/8/2010531TANT
9Sum-15689.08
10
11101120131/07/2010-2,140.37Food Accrual
12101120131/07/201018,446.90Food Stock
13101120131/08/2010-20,086.75Food Stock
14Sum-3,780.22
15
1610112027/8/201086.59ABC FOOD
17101120218/08/2010595.8FRESH DISP
18Sum682.39
19
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub MG08Sep58V2()
' MickG, 09/08/2010, ME493485
' Modified by hiker95, 09/08/2010, ME493485
Dim rng As Range
Dim Rw As Long
Dim R As Range
Dim LR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A1:E" & LR)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Rw = rng.Count + 1 To 2 Step -1
  With Range("A" & Rw)
    If Not .Value = .Offset(-1).Value Then
      .Resize(2).EntireRow.Insert
    End If
  End With
Next Rw
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Set rng = rng.SpecialCells(xlCellTypeConstants)
Dim tot
For Each R In rng.Areas
  R(R.Count + 1, 2) = "Sum"
  R(R.Count + 1, 3) = Application.Sum(R.Offset(, 2))
Next R
Range("A2").Resize(2).EntireRow.Delete
Application.ScreenUpdating = True
End Sub


Then run the "MG08Sep58V2" macro.
 
Upvote 0
Hi hiker95 and Mick,

thank you both for your help!!!! Sorting the information before the split was a perfect solution!!!

Just another two questions:
1) can I change the description "Sheet1" in the code with a dynamic one for example the "active worksheet"?

2) the macro sums the intervals and return a fixed value. Is it possible to have the formula "sum" instead of a fixed value, just in case I have to change the figures manually?

Thank you guys!
 
Upvote 0
Simona,

I have not been able to adjust KickG's code to insert a Sum formula.

However, I have another macro, but I am using columns G, H, and I as a helper column to create an array.

And, this macro will also work on the active worksheet (but, there is no error checking to check valid data).


Sample data before the macro:


Excel Workbook
ABCDE
1Analysis CodeTransaction DateBase Amount?????Description
2101110131/08/20102,047.50BOULANG
3101110120/07/201027.53ANT PATISSERIE
410111024/8/2010531TANT
5101110231/07/2010-1,350.48Soft Drink Accrual
6101110231/08/2010-14,869.60Soft Drinks Stock
7101120131/08/2010-20,086.75Food Stock
8101120131/07/201018,446.90Food Stock
9101120131/07/2010-2,140.37Food Accrual
10101120218/08/2010595.8FRESH DISP
1110112027/8/201086.59ABC FOOD
12
13
14
15
16
17
18
19
Sheet1





After the new macro:


Excel Workbook
ABCDE
1Analysis CodeTransaction DateBase Amount?????Description
2101110120/07/201027.53ANT PATISSERIE
3101110131/08/20102,047.50BOULANG
4Sum2,075.03
5
6101110231/07/2010-1,350.48Soft Drink Accrual
7101110231/08/2010-14,869.60Soft Drinks Stock
810111024/8/2010531TANT
9Sum-15,689.08
10
11101120131/07/2010-2,140.37Food Accrual
12101120131/07/201018,446.90Food Stock
13101120131/08/2010-20,086.75Food Stock
14Sum-3,780.22
15
1610112027/8/201086.59ABC FOOD
17101120218/08/2010595.8FRESH DISP
18Sum682.39
19
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Option Base 1
Sub CreateReport()
' stanleydgromjr, 09/09/2010, EF744797
Dim LR As Long, LR2 As Long, a As Long, MyTot As Long, SR As Long, ER As Long
Dim MyG
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("E2:E" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
  .SetRange Range("A1:E" & LR)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1"), Unique:=True
LR2 = Cells(Rows.Count, 7).End(xlUp).Row
With Range("H2:H" & LR2)
  .FormulaR1C1 = "=MATCH(RC7,R1C1:R" & LR & "C1,0)"
  .Value = .Value
End With
With Range("I2:I" & LR2 - 1)
  .FormulaR1C1 = "=R[1]C[-1]-1"
  .Value = .Value
End With
Range("I" & LR2) = LR
MyG = Range("H2:I" & LR2)
Range("G1:I" & LR2).ClearContents
For a = UBound(MyG) To LBound(MyG) Step -1
  SR = MyG(a, 1)
  ER = MyG(a, 2)
  Range("B" & ER + 1 & ":B" & ER + 2).EntireRow.Insert
  Range("B" & ER + 1) = "Sum"
  Range("C" & ER + 1).Formula = "=SUM(C" & SR & ":C" & ER & ")"
Next a
Erase MyG
Application.ScreenUpdating = True
End Sub


Then run the "CreateReport" macro.


If you are using columns G, H, and I for something else, then what three columns can I use to create my array?
 
Upvote 0
Simona,

OK, I got it to work with the active worksheet and MickG's original code.


Sample data after the new macro:


Excel Workbook
ABCDE
1Analysis CodeTransaction DateBase Amount?????Description
2101110120/07/201027.53ANT PATISSERIE
3101110131/08/20102,047.50BOULANG
4Sum2,075.03
5
6101110231/07/2010-1,350.48Soft Drink Accrual
7101110231/08/2010-14,869.60Soft Drinks Stock
810111024/8/2010531TANT
9Sum-15,689.08
10
11101120131/07/2010-2,140.37Food Accrual
12101120131/07/201018,446.90Food Stock
13101120131/08/2010-20,086.75Food Stock
14Sum-3,780.22
15
1610112027/8/201086.59ABC FOOD
17101120218/08/2010595.8FRESH DISP
18Sum682.39
19
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Sub MG08Sep58V3()
' MickG, 09/08/2010, ME493485
' Modified by hiker95, 09/09/2010, ME493485
Dim rng As Range
Dim Rw As Long
Dim R As Range
Dim LR As Long, SR As Long, ER As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("E2:E" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & LR) _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
  .SetRange Range("A1:E" & LR)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Rw = rng.Count + 1 To 2 Step -1
  With Range("A" & Rw)
    If Not .Value = .Offset(-1).Value Then
      .Resize(2).EntireRow.Insert
    End If
  End With
Next Rw
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Set rng = rng.SpecialCells(xlCellTypeConstants)
Dim tot
For Each R In rng.Areas
  R(R.Count + 1, 2) = "Sum"
  SR = R.Row
  ER = SR + R.Count - 1
  R(R.Count + 1, 3).Formula = "=Sum(C" & SR & ":C" & ER & ")"
Next R
Range("A2").Resize(2).EntireRow.Delete
Application.ScreenUpdating = True
End Sub


Then run the "MG08Sep58V3" macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Comparing columns of data between worksheets and returning value when matched.
Replies
7
Views
949
Ronanm
R
A
  • Question Question
VLOOKUP against a date range and document name based on name and publication date of a book to return the version number of the book
Replies
3
Views
746
ayeready
A
P
  • Question Question
Delete entire row
Replies
2
Views
804
Panoos64
P
P
  • Question Question
Complete calendar month's dates
2
Replies
17
Views
2K
elynoy
E
G
  • Question Question
SUMIF name and total hours are within specific date range
2 3
Replies
21
Views
2K
Glasgow Girl
G

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top