Genereren BOM

[hermes79]

Goede morgen,

ik ben momenteel mijn hoofd aan het breken over hoe ik een BOM (Bill Of Material) kan genereren uit data die ik ter beschikking heb.
Grote probleem is dat het niveau/level niet opgenomen is in de data

Tabblad DATA: logischerwijs de data die ik ter beschikking heb
Tabblad Index: hier plaats ik in cel B1 het hoofdartikel waarna ik de onderliggende componenten wens te genereren

Het eerste niveau lukt mij uiteraard. Maar ik weet dat de artikelen in cel B7, B18 & B19 nog opgebouwd zijn uit onderliggende componenten, sommige zelf tot 3 niveaus.
Helaas heb ik geen idee hoe ik ook deze lijnen kan tevoorschijn halen.

Ik besef dat ik nog absoluut niet ver sta, maar misschien kan iemand mij alvast een hint geven waarmee ik verder kan.

Alvast bedankt!

 

[VenA]

Met een macro obv het hoofdartikel in B1. Als dit de verwachte uitkomst is dan is het nog wel te vereenvoudigen om meer levels te doorzoeken.

 

[hermes79]

Dag VenA,

bedankt alvast.
Via deze macro heb ik idd een lijst van alle artikelen die betrokken zijn in het hoofdartikel.
Maar ik had graag een boomstructuur bekomen, zoals je kan zien in m'n tabblad Index (2)

vriendelijke groeten,

hermes79

 

[VenA]

Even een ander idee. Is een draaitabel geen optie? De lay-out is dan wel anders dan jij voor ogen hebt maar is wel dynamischer.

Even als voorbeeld in de tab 'Dt_Index'

 

[hermes79]

VenA,

op zich kan een draaitabel idd eveneens een oplossing vormen.
Beperking die zicht stelt is echter dat de niveaus onder elkaar staan en dat bijvb de regels 22-32 niet net onder het hoofdartikel op regel 9 staan.

Het is de bedoeling om de opbouw van een artikel, voor de gebruiker, visueel zo duidelijk als mogelijk te maken

 

[VenA]

Dan zo?

Sub VenA()
Dim j0 As Long, j1 As Long, j2 As Long, s As String, ar
  s = Sheets("Index").Cells(1, 2).Value
  ar = Sheets("DATA").Cells(1).CurrentRegion
  With CreateObject("scripting.dictionary")
    For j0 = 2 To UBound(ar)
      If ar(j0, 1) = s Then
        .Item(.Count) = Array(ar(j0, 2), "", "", ar(j0, 3), ar(j0, 4), ar(j0, 5), ar(j0, 6))
        For j1 = 2 To UBound(ar)
          If ar(j1, 1) = ar(j0, 2) Then
            .Item(.Count) = Array("", ar(j1, 2), "", ar(j1, 3), ar(j1, 4), ar(j1, 5), ar(j1, 6))
            For j2 = 2 To UBound(ar)
              If ar(j2, 1) = ar(j1, 2) Then .Item(.Count) = Array("", "", ar(j2, 2), ar(j2, 3), ar(j2, 4), ar(j2, 5), ar(j2, 6))
            Next j2
          End If
        Next j1
      End If
    Next j0
    Sheets("Index (2)").Cells(2, 11).Resize(.Count, 7) = Application.Index(.items, 0, 0)
  End With
End Sub

 

[hermes79]

VenA,

dit is perfect!
Echter nog één niet onbelangrijk detail.

Ik wil aantallen kunnen opnemen.

Meer specifiek wens ik bijvb van het hoofdartikel 100 stuks. Maar stel dat een welbepaald subartikel 2x is opgenomen in het finaal product, dan dienen de onderdelen niet maal 100 maar maal 200 vermenigvuldigd te worden.

Eens dit lukt kan ik verder met het koppelen van andere data/tabellen.

Bedankt!

 

[VenA]

Da's dan een mooie uitdaging die je zelf eerst maar eens mag proberen op te lossen.

Zoek het even in ar(j(x),4) * aantal

 

[hermes79]

Owkay, dank u voor de tip!

 

[rebmog]

De oplossing van VenA werkt in de gegeven situatie natuurlijk goed, maar moet aangepast worden als het aantal levels toeneemt. Het is daarom aan te bevelen om een BOM recursief te genereren. Dit werkt dan onafhankelijk van het aantal levels. Zie bijlage voor een voorbeeld.

 

[jkpieterse]

Lijkt een eitje middels PowerQuery en PowerPivot. Twee queries, één die de unieke hoofdartikelnummers bepaalt en éé die alle artikelgegevens "ophaalt" en die twee dan aan elkaar gekoppeld via 1 relatie.

 

[hermes79]

De oplossing van VenA werkt in de gegeven situatie natuurlijk goed, maar moet aangepast worden als het aantal levels toeneemt. Het is daarom aan te bevelen om een BOM recursief te genereren. Dit werkt dan onafhankelijk van het aantal levels. Zie bijlage voor een voorbeeld.

Bedankt Rebmog,

dit is visueel idd ook heel goed.
Maar ik vrees dat het in deze moeilijker is om de aantallen door te trekken.

Bijvb. ik wens artikel X 10 keer, een bepaald component komt echter 7 keer terug, dit wil zeggen dat ik van de onderliggende componenten 70 stuks nodig heb

 

[rebmog]

Het doortrekken van de aantallen is in deze recursieve functie geen probleem. Dat is een kwestie van een extra variabele toevoegen waarin je dit aantal opneemt. Bij de volgende aanroep geef je dan dit aantal vermenigvuldigd met het aantal van het betreffende artikel weer door.

 

[hermes79]

Het lukt mij helaas niet, te weinig onderlegt in het schrijven van macro's

 

[rebmog]

Ik heb daarom zelf het voorbeeldje maar even aangepast.
In de bijlage een versie waarin ook het totaal aantal wordt meegenomen. Om het effect te kunnen zien heb ik op sheet data het aantal in cel D178 verandert van 1 naar 10.