Help with multi-tier recursive totals using VBA

[micklebt]

Let's call the numbering system in column 1 a work breakdown structure (WBS) or hierarchical taxonomy.

1. There is no fixed number of elements below a top level,
2. There is no fixed number of sub-indents.
3. Therefore, the maximum length of 'Number' field is variable and unknown.

I am attempting to calculate item and sub-item 'Amount' totals. Each higher WBS level is always the sum of the elements from the next lower tier only. Each total is a 'rolled up' amount from the levels below it, which are in turn rolled up from levels below, etc.

The dollar amount for any top level 'Thing n' (1) is the sum of values for all items in the hierarchical level below that level (1 = 1.1 + 1.3), which is in turn sum of values for all items in the next hierarchical level below that level (1.1 = 1.1.1 + 1.1.3 + 1.1.5 + 1.1.7), and so on.

For any parent element with children below it, the parent amount must equal the sum of its children, and so on back up the tree.

I believe a VBA function is the likeliest solution, though the actual code is beyond my skill level. Thanks in advance for any assistance.

Brian

[TABLE="width: 279"]
<tbody>[TR]
[TD="class: xl66, width: 70, bgcolor: #4F81BD"]Number[/TD]
[TD="width: 114, bgcolor: #4F81BD"]Description[/TD]
[TD="class: xl65, width: 95, bgcolor: #4F81BD"] Amount[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #DCE6F1"]1[/TD]
[TD="class: xl71, bgcolor: #DCE6F1"] Thing 1[/TD]
[TD="class: xl71, bgcolor: #DCE6F1"] $ 56.25[/TD]
[/TR]
[TR]
[TD="class: xl66"]1.1[/TD]
[TD="class: xl72"]Sub 1a[/TD]
[TD="class: xl73"] $ 36.25[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]1.1.1[/TD]
[TD="class: xl69, bgcolor: #DCE6F1"]Sub-Sub[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] $ 2.25[/TD]
[/TR]
[TR]
[TD="class: xl66"]1.1.3[/TD]
[TD="class: xl69"]Sub-Sub[/TD]
[TD="class: xl65"] $ 15.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]1.1.3.1[/TD]
[TD="class: xl74, bgcolor: #DCE6F1"]Sub-Sub-Sub[/TD]
[TD="class: xl70, bgcolor: #DCE6F1"] $ 10.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]1.1.3.3[/TD]
[TD="class: xl74"]Sub-Sub-Sub[/TD]
[TD="class: xl70"] $ 5.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]1.1.5[/TD]
[TD="class: xl69, bgcolor: #DCE6F1"]Sub-Sub[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] $ 2.50[/TD]
[/TR]
[TR]
[TD="class: xl66"]1.1.7[/TD]
[TD="class: xl69"]Sub-Sub[/TD]
[TD="class: xl65"] $ 1.50[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]1.3[/TD]
[TD="class: xl72, bgcolor: #DCE6F1"]Sub 1b[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"] $ 20.00[/TD]
[/TR]
[TR]
[TD="class: xl71"] 2[/TD]
[TD="class: xl71"] Thing 2[/TD]
[TD="class: xl71"] $ 65.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]2.1[/TD]
[TD="class: xl72, bgcolor: #DCE6F1"]Sub 2a[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"] $ 40.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]2.1.1[/TD]
[TD="class: xl69"]Sub-Sub[/TD]
[TD="class: xl65"] $ 10.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]2.1.3[/TD]
[TD="class: xl69, bgcolor: #DCE6F1"]Sub-Sub[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] $ 10.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]2.1.5[/TD]
[TD="class: xl69"]Sub-Sub[/TD]
[TD="class: xl65"] $ 10.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]2.1.7[/TD]
[TD="class: xl69, bgcolor: #DCE6F1"]Sub-Sub[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] $ 10.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]2.3[/TD]
[TD="class: xl72"]Sub 2b[/TD]
[TD="class: xl73"] $ 25.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]2.3.1[/TD]
[TD="class: xl69, bgcolor: #DCE6F1"]Sub[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] $ 25.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi Brian,

I managed without VBA, but did need an array formula... So I copied your data to an empty sheet, starting the headers from A1. My first extra column is D, that is where I wanted to calculate the level of your node (that's the term I would use, it's a bit like a treeview, talking about nodes, mothers (the level above) and orphans (nodes without a level above)) .

D2 has the formula: =LEN(A2)-LEN(SUBSTITUTE(A2,".",""))
That basically counts the number of dots in your node name, 0 is your highest level. E.g. 1.1.3 would be level 2 (down from the top). It's purely informative.

Next, I wanted to know whether the node had any parents, so I made a formula to find out whether the node was at the lowest level in cell E2:
=IF(COUNTIF($A$2:$A$18,A2&"*")=1,1,0)
Here the bottom nodes (that could have numbers you want to add up) show a 1, other nodes show a 0. Note: this is just informative, no need to use the formulae afterwards.

Next thing I did was to put your numbers from column C in column F, thereby leaving out the calculated ones (like your row 2 and 3 that are actually wrong in your example...), so I only have the pure data to add up.

Last step: the actual calculation in column G, goes with an array formula. Type the formula and press CTRL+SHIFT+ENTER after you typed the formula, not just ENTER:
=SUM(IF(LEFT($A$2:$A$18,LEN(A2))=A2,$F$2:$F$18,0))
What is happening: it's a conditional sum of
LEFT($A$2:$A$18,LEN(A2))=A2 : the left bit of the nodes that need to be added up is equal to the current node. So in case of node 1.1, the formula will check whether the left 3 characters match 1.1

Cheers,

Koen

 

[shg]

How about ...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Number​

[/td][td]

Description​

[/td][td]

Amount​

[/td][td]

Ext Amt​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]1[/td][td]Thing 1[/td][td][/td][td]

$77.50​

[/td][td]D2 and down: =IF(C2<>"", "", SUMIF(A3:A$18, A2 & "*", C3:C$18))[/td][/tr]

[tr][td]

3​

[/td][td]1.1[/td][td]Sub 1a[/td][td]

$36.25​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]1.1.1[/td][td]Sub-Sub[/td][td]

$2.25​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]1.1.3[/td][td]Sub-Sub[/td][td][/td][td]

$15.00​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]1.1.3.1[/td][td]Sub-Sub-Sub[/td][td]

$10.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]1.1.3.3[/td][td]Sub-Sub-Sub[/td][td]

$5.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]1.1.5[/td][td]Sub-Sub[/td][td]

$2.50​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]1.1.7[/td][td]Sub-Sub[/td][td]

$1.50​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]1.3[/td][td]Sub 1b[/td][td]

$20.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]2[/td][td]Thing 2[/td][td][/td][td]

$65.00​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]2.1[/td][td]Sub 2a[/td][td][/td][td]

$40.00​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]2.1.1[/td][td]Sub-Sub[/td][td]

$10.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]2.1.3[/td][td]Sub-Sub[/td][td]

$10.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]2.1.5[/td][td]Sub-Sub[/td][td]

$10.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]2.1.7[/td][td]Sub-Sub[/td][td]

$10.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]2.3[/td][td]Sub 2b[/td][td][/td][td]

$25.00​

[/td][td][/td][/tr]

[tr][td]

18​

[/td][td]2.3.1[/td][td]Sub[/td][td]

$25.00​

[/td][td][/td][td][/td][/tr]
[/table]

The WBS column should be formatted as text beforehand (or, if after the fact, converted to text, not just formatted as text). Each WBS level must be the same length; e.g., if level 2 goes 1 to 12, it must be 01 through 12.

 

[micklebt]

Hi Koen,

What a brilliant, clean, and EFFECTIVE solution! Many, many thanks. Thanks as well for including the logic of your solution as well. I will definitely study it and learn from it.

Two questions:

1. Can you help me to understand the "*" (asterisk) usage? I have never seen this before. Your formula cell E2:
   =IF(COUNTIF($A$2:$A$18,A2&"*")=1,1,0)
2. Can you recommend a good way to change the final cell references to refer to table columns, versus the row/column syntax. I attempted a few ways without success.

Again, many thanks Koen.

Brian

Hi Brian,

I managed without VBA, but did need an array formula... So I copied your data to an empty sheet, starting the headers from A1. My first extra column is D, that is where I wanted to calculate the level of your node (that's the term I would use, it's a bit like a treeview, talking about nodes, mothers (the level above) and orphans (nodes without a level above)) .

D2 has the formula: =LEN(A2)-LEN(SUBSTITUTE(A2,".",""))
That basically counts the number of dots in your node name, 0 is your highest level. E.g. 1.1.3 would be level 2 (down from the top). It's purely informative.

Next, I wanted to know whether the node had any parents, so I made a formula to find out whether the node was at the lowest level in cell E2:
=IF(COUNTIF($A$2:$A$18,A2&"*")=1,1,0)
Here the bottom nodes (that could have numbers you want to add up) show a 1, other nodes show a 0. Note: this is just informative, no need to use the formulae afterwards.

Next thing I did was to put your numbers from column C in column F, thereby leaving out the calculated ones (like your row 2 and 3 that are actually wrong in your example...), so I only have the pure data to add up.

Last step: the actual calculation in column G, goes with an array formula. Type the formula and press CTRL+SHIFT+ENTER after you typed the formula, not just ENTER:
=SUM(IF(LEFT($A$2:$A$18,LEN(A2))=A2,$F$2:$F$18,0))
What is happening: it's a conditional sum of
LEFT($A$2:$A$18,LEN(A2))=A2 : the left bit of the nodes that need to be added up is equal to the current node. So in case of node 1.1, the formula will check whether the left 3 characters match 1.1

Cheers,

Koen

 

[Rijnsent]

Hi Brian,

with the help of the formula provided by shg, see the file "RecursiveSum.xlsx" here: https://www.dropbox.com/sh/l7ywfwzfk5j20sr/dJ6Jk0NGyM (that's where I store all kind of Excel examples I answered on the forum)

And to answer your questions:
1) COUNTIF($A$2:$A$18,A2&"*") : Basically we're talking about condition (second bit of the formula) for a SUMIF(S), COUNTIF(S) etc. You could put there "JOHN", in that case the formula would count all JOHNs. But you could also put "J*", in that case the formula would count everybody with a name starting with a J, or "*J*" for anybody with a J somewhere in their name. And what's happening here is exactly that: the condition is built up as a string with the value of cell A2 and adds a *. Other examples that you could use as a criteria are "<>Bananas" (not equal to Bananas), "<>0" (not equal to 0), ">5", etc. It's basically glueing together text .

2) See the file, I added both my array formula and the (way easier) solution of shg.

Cheers,

Koen

 

[micklebt]

Shg,

Thanks for the alternative approach. Interestingly, your solution provides totals only for those parent nodes with children - which may prove valuable in a way I hadn't thought of.

In the end, I may choose a hybrid mash-up of the two suggested approaches.

This is for a proof-of-concept I'm developing. The underlying goal is to see whether, by using a properly structured hierarchical breakdown, we can achieve many of the benefits and capabilities that would typically be achieved (only) via a relational database. I am getting very close.

The current notional use case is a logistics warehouse vendor. Some inbound inventory gets the 'full treatment' - at level 1 or level 2, and all inclusive children. Other things get only a subset of the 'full treatment' (and therefore reduced pricing). Still other things get a value-added super-set of 'full treatment', especially on the outbound shipping side.

I am trying to devise a simple solution for the logistics customer. Their WBS will contain the complete 'universe' of their solution set. Then, they would enter pricing per each individual contract they service.

You and Koen are pointing me in the right direction, and I believe the design is workable. Thanks a bunch.

Brian

 

[shg]

A parent node without children is not a parent node, it's a leaf node. If you want to see the leaf values carried to the right,

=IF(C2<>"", C2, SUMIF(A3:A$18, A2 & "*", C3:C$18))