PDA

View Full Version : BOM Challenge



tommy1234
08-17-2014, 12:54 AM
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
08-17-2014, 02:42 AM
i messed this up so removed for the time being

Bob Phillips
08-18-2014, 12:59 AM
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
08-19-2014, 12:21 AM
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(SUBSTITUTE(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
08-19-2014, 01:45 AM
Is that a launch code for something?

westconn1
08-19-2014, 03:16 AM
i can follow most of it, but iferror does not appear to work for my version of excel

Krishna Kumar
08-19-2014, 03:38 AM
=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
08-19-2014, 04:44 AM
should substitute work on an array?

Paul_Hossler
08-19-2014, 07:15 AM
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
08-20-2014, 06:36 AM
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/formula-challenge-024-sum-of-items-based-on-hierarchy-level.18447/

Paul_Hossler
08-20-2014, 06:51 AM
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