Multiple Subtotals using VBA

dark_horizon

New Member
Joined
Mar 27, 2014
Messages
15
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sr

[/TD]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]L1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]L1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]L2
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]L3
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]L3
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]L4
[/TD]
[TD]15
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]L3
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]L4
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]L5
[/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi All

Above is an example of input data I extract from a database.Column A has multiple levels (named L1, L2, L3, L4, L5). Column B has values. The way it functions is,
L2 = Sum of all L1 above it, till you encounter another L2above.
L3 = sum of all L2 above it till you encounter another L3above.

Similar for L4. The final level is L5 which has sum of all L4 in the column.

Also, sometimes there is a standalone L2 (for which no L1 breakup details are available), and it has to be considered as it is. Example – Cell B10,which has a standalone value of 2, and there is immediately another L2 above it in Cell B9, which is a sum of L1 above it

The issue is sometimes the data is incorrect and an L2 might not be the sum of all L1 above it (like a L1 row might have been missed by the database). I need to verify in column C if such an error has taken place.

So my output in column C should have values same as Column B for L2, L3, L4, L5 if there is no error. Or different values, if there is error.

The range is dynamic and runs into hundreds of rows.

Any macro or pre-existing excel formula can accomplish this?

I would be very grateful for any help/ code or pointers Ican get for this problem. Thank you.
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
So I managed to start working on it and preparedthe following code. Need help pertaining to the comments mentioned in the code:

Code:
Sub Ttals()
lastrow = Worksheets("EMEA").Cells(Rows.Count,"AB").End(xlUp).Row
Debug.Print lastrow
For i = 2 To lastrow
If Cells(i, 1) = "L1" Then
    Cells(i, 3) =Cells(i, 2) 'This is pasting L1 values from column B/2 to column C/3
    
ElseIf Cells(i, 1) = "L2" Then
'Insert a function to search rows above this cell for values:
'If immediately above - L2/L3/L4/L5/L6 are encountered, paste value from column B/2 for this row
'If immediately above L1 is encountered, keep going above till more L1 are encoutered,
'and when any other L are encountered, stop going up and take a sum of all L1 in this range and post the sum
 
ElseIf Cells(i, 1) = "L3" Then
'Insert similar function which I can modify myself once I get the above function for L2
 
End If
Next
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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