CODEWITHSUNDEEP
excelvbasubtotal
dark_horizon
dark_horizon

Reputation: 3

Specific subtotals using VBA

enter image description here

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 L2 above. L3 = sum of all L2 above it till you encounter another L3 above.

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 I can get for this problem.

The following is part code which I created. Need your assistance for the comments part. Thank you for the assistance.

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

Upvotes: 0

Views: 192

Answers (1)

JvdV
JvdV

Reputation: 75840

Here is a formula you can use:

{=IF(VALUE(RIGHT(A1,1))>1,IF(COUNTIFS(INDIRECT("A1:A"&ROW()),A1)=1,SUMIFS(INDIRECT("B1:B"&ROW()),INDIRECT("A1:A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1),SUMIFS(INDIRECT("B"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":B"&ROW()),INDIRECT("A"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1)),B1)}

Tried different things to check value of row above but array formula has gotten the better of me, so below is a check for 0:

{=IF(IF(VALUE(RIGHT(A1,1))>1,IF(COUNTIFS(INDIRECT("A1:A"&ROW()),A1)=1,SUMIFS(INDIRECT("B1:B"&ROW()),INDIRECT("A1:A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1),SUMIFS(INDIRECT("B"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":B"&ROW()),INDIRECT("A"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1)),B1)=0,B1,IF(VALUE(RIGHT(A1,1))>1,IF(COUNTIFS(INDIRECT("A1:A"&ROW()),A1)=1,SUMIFS(INDIRECT("B1:B"&ROW()),INDIRECT("A1:A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1),SUMIFS(INDIRECT("B"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":B"&ROW()),INDIRECT("A"&LARGE(IF($A$1:INDIRECT("A"&ROW())=A1,ROW($A$1:INDIRECT("A"&ROW()))-MIN(ROW($A$1:INDIRECT("A"&ROW())))+1),2)&":A"&ROW()),"L"&VALUE(RIGHT(A1,1))-1)),B1)}

Notice it's an array formula! Paste in cel C1 as such and drag it down.

It's quite a lengthy one :P

Upvotes: 1

Related Questions