Explode BOM lines in Excel to understand sales data by ship date

[tanujath]

Hi All,
I am having a hard time exploding BOMs into BOM lines to explode my data by Ship date. Let me show an example below of what I am looking to do:

Table 1
SALES ORDER NO. ITEM NUMBER QTY SHIP DATE
100 KIT-1 10 9/31/2021
200 KIT-2 20 9/17/2021

Table 2
ITEM NUMBER BOM LINES QTY NEEDED
KIT-1 Item 1 1
KIT-1 Item 2 2
KIT-1 Item 3 5
KIT-2 Item 4 3
KIT-2 Item 5 1

Table 3
SALES ORDER NO. ITEM NUMBER BOM LINES QTY SHIPDATE
100 KIT-1 Item 1 10 9/31/2021
100 KIT-1 Item 2 20 9/31/2021
100 KIT-1 Item 3 50 9/31/2021
200 KIT-2 Item 4 60 9/17/2021
200 KIT-2 Item 5 20 9/17/2021

I have Table 1 and 2 available, need to explode Table 1 to Table 3 using Table 2. If anyone have any ideas I would love to try them out. VBA or formulas, anything is fine.

 

[DanteAmor]

Welcome to the MrExcel Message Board!

Let's see if I got the idea correctly.
You have 3 sheets Sheet1 (Table1), Sheet2 (Table2) and Sheet3 (Table3).
All headings in row 1 and data start in cell A2. Check the images:

Dante Amor
	A	B	C	D
1	SALES ORDER NO.	ITEM NUMBER	QTY	SHIP DATE
2	100	KIT-1	10	30/09/2021
3	200	KIT-2	20	17/09/2021
Sheet1

Dante Amor
	A	B	C	D
1	ITEM NUMBER	NUMBER	BOM LINES	QTY NEEDED
2	KIT-1	Item	1	1
3	KIT-1	Item	2	2
4	KIT-1	Item	3	5
5	KIT-2	Item	4	3
6	KIT-2	Item	5	1
Sheet2

Dante Amor
	A	B	C	D	E	F
1	SALES ORDER NO.	ITEM NUMBER	NUMBER	BOM LINES	QTY	SHIP DATE
2	100	KIT-1	Item	1	10	30/09/2021
3	100	KIT-1	Item	2	20	30/09/2021
4	100	KIT-1	Item	3	50	30/09/2021
5	200	KIT-2	Item	4	60	17/09/2021
6	200	KIT-2	Item	5	20	17/09/2021
Sheet3

Try this macro:

Sub Explode_BOM_Lines()
  Dim a As Variant, b As Variant, c As Variant
  Dim dic As Object
  Dim i As Long
 
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("D" & Rows.Count).End(3)).Value
  b = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("D" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(b, 1), 1 To 6)
  Set dic = CreateObject("Scripting.Dictionary")
 
  For i = 1 To UBound(a, 1)
    dic(a(i, 2)) = a(i, 1) & "|" & a(i, 3) & "|" & a(i, 4)
  Next
 
  For i = 1 To UBound(b, 1)
    c(i, 1) = Split(dic(b(i, 1)), "|")(0)
    c(i, 2) = b(i, 1)
    c(i, 3) = b(i, 2)
    c(i, 4) = b(i, 3)
    c(i, 5) = Split(dic(b(i, 1)), "|")(1) * b(i, 4)
    c(i, 6) = Split(dic(b(i, 1)), "|")(2)
  Next
 
  Sheets("Sheet3").Range("A2").Resize(UBound(b, 1), 6).Value = c
End Sub

 

[tanujath]

Hi Dante Amor,

Thank you, the code works like magic!!

I have a doubt though, the tables I provided above are examples. My actual tables have these dimensions:

table 1:
Columns A to AD
Rows: 20,000

Table 2:
Columns A to F
Rows: 1500

Since Table 1 is sales data, the item numbers or kits can repeat as well. Could you provide a code for this?