Multiple Subtotals using VBA

[dark_horizon]

[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.

 

[dark_horizon]

So I managed to start working on it and preparedthe following code. Need help pertaining to the comments mentioned in the 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

 

[dark_horizon]

Any solution?