BOM Challenge

[tommy1234]

Hello
I have a challenge that a can't solve.
i have a BOM with many level (the levels shown in column 'A')
i need to calculate the sum prices of each level to its upper level (the final result is shown in column 'D') and each part price is shown in column 'C'.
for example :
line 19 should be the sum of : line 20 + line 19
line 15 should be the sum of : line 16 + line 15


line 8 should be the sum of : line 19 + line 18 +line 17 + line 15 +line 14 + line 13 +line 9 (all the levels below, in this case level 3)


until now, i haven't found a solution, please help...


thank you

[westconn1]

i messed this up so removed for the time being

[Bob Phillips]

I'm confused. You say that 'line 8 should be the sum of : line 19 + line 18 +line 17 + line 15 +line 14 + line 13 +line 9', which in your workbook shows a value of 229.19. But if I add those lines I get 208.82. If I add up all of the values for 3,4,5 below line 8, then I get 229.19. SO which is it meant to be?

[Krishna Kumar]

In D2 and copied down,

=IF(A2=1,SUM($C$2:$C$20),SUMPRODUCT(--(--(SUBSTITUTE(A2:INDEX(A2:$A$20,IFERROR(MATCH(SUBSTITUTE(A2,".",""),INDEX(SUB STITUTE(A3:$A$20,".",""),0,0),0),COUNTA(A2:$A$20))),".",""))>=--SUBSTITUTE(A2,".","")),C2:INDEX(C2:$C$20,IFERROR(MATCH(SUBSTITUTE(A2,".","" ),INDEX(SUBSTITUTE(A3:$A$20,".",""),0,0),0),COUNTA(A2:$A$20)))))

[Aussiebear]

Is that a launch code for something?

[westconn1]

i can follow most of it, but iferror does not appear to work for my version of excel

[Krishna Kumar]

=IF(A2=1,SUM($C$2:$C$20),SUMPRODUCT(--(--(SUBSTITUTE(A2:INDEX(A2:$A$20,IF(ISERROR(MATCH(SUBSTITUTE(A2,".",""),INDEX(SUBSTITUTE(A3:$A$20,".",""),0,0),0)),COUNTA(A2:$A$20),MATCH(SUBSTITUTE(A2,".",""),INDEX(SUBSTITUTE(A3:$A$20,".",""),0,0),0))),".",""))>=--SUBSTITUTE(A2,".","")),C2:INDEX(C2:$C$20,IF(ISERROR(MATCH(SUBSTITUTE(A2,".",""),INDEX(SUBSTITUTE(A3:$A$20,".",""),0,0),0)),COUNTA(A2:$A$20),MATCH(SUBSTITUTE(A2,".",""),INDEX(SUBSTITUTE(A3:$A$20,".",""),0,0),0))))) 

[westconn1]

should substitute work on an array?

[Paul_Hossler]

Little on the brute force side, but should a starting point

Option Explicit
Sub BOM_Cost()
    Dim aBOM As Variant
    Dim iLevel As Long, iMaxLevel As Long, iRow As Long, iRow2 As Long, iNumRows As Long
 
    'load array with data
    aBOM = Cells(1, 1).CurrentRegion.Value
    
    iNumRows = UBound(aBOM, 1)

    'remove dots, convert level to long, make total = unit
    'determine highest level
    For iLevel = 2 To iNumRows
        aBOM(iLevel, 1) = Replace(aBOM(iLevel, 1), ".", vbNullString)
        aBOM(iLevel, 1) = CLng(aBOM(iLevel, 1))
        aBOM(iLevel, 4) = aBOM(iLevel, 3)
        
        If aBOM(iLevel, 1) > iMaxLevel Then iMaxLevel = aBOM(iLevel, 1)
    
    Next iLevel
    For iLevel = iMaxLevel To 1 Step -1
    
        For iRow = 2 To iNumRows - 1
            
            If aBOM(iRow, 1) = iLevel Then
                                
                For iRow2 = iRow + 1 To iNumRows
                    If aBOM(iRow2, 1) = iLevel + 1 Then
                        aBOM(iRow, 4) = aBOM(iRow, 4) + aBOM(iRow2, 4)
                                        
                    ElseIf aBOM(iRow2, 1) = iLevel Then
                        Exit For
                    End If
                Next iRow2
            End If
    
        Next iRow
    
    Next iLevel

    'test
    Cells(1, 12).ClearContents
    Cells(1, 12).Resize(UBound(aBOM, 1), UBound(aBOM, 2)).Value = aBOM
End Sub

[shrivallabha]

Formula in #7 by Krishna Kumar seems to be working fine.

And here's one challenge cooking up on it on Chandoo
http://chandoo.org/forum/threads/for...y-level.18447/

[Paul_Hossler]

For myself, I find extremely long and complex formulas with lots of nested parens, IF's, other worksheet formulas very hard to construct and even harder to maintain

When the data is no longer in A1 : C20 I would find changing the references in the #7 formula to the new ones more trouble than it's worth

I think the 20 rows are just an example, and that the actually number of rows will vary. So I would consider a more flexible solution that doesn't depend on explicit addresses embedded in formulas

But that's just my opinion

Not saying that my suggested VBA is perfect (since there was only a single data set to use for testing), but it could be a prototype algorithm for a more robust solution