Batch Inventory in excel

[vikash_05]

Hi, can any one help me with my office batch inventory solution in excel.
Dispatch data is available and i need to summarize it in batch summary.


Thanks a Lot.

 

[vikash_05]

First sheet is "Dispatch Data"
2nd sheet is "Size-8"
3rd sheet is "Size-9"
4th sheet is "Size-0"

total 4 sheets

 

[DanteAmor]

How many Batch-Qty columns can you have, only 4 or can there be more?
You can put the data here with XL2BB tool. That way I can use the data, I cannot copy it from an image.

Put here the data from the "Dispatch Data" and "Size-8" sheet to see in which cell they start.

 

[vikash_05]

DISPATCH DATA

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Dispatch Data
2	Date	Challan No	Size	Batch No.	Qty	Batch No	Qty	Batch No	Qty	Batch No	Qty	Total
3	01-Apr	54321	C	C-101	600	C-102	2500	C-105	2000	C-104	500	5600
4	01-Apr	54322	C	C-102	1000	C-104	1300	C-106	1200			3500
5	02-Apr	54323	A	A-201	300	A-203	1500					1800
6	03-Apr	54324	B	B-301	1000	B-302	1200	B-303	500			2700
7	04-Apr	54325	A	A-201	200	A-203	500					700
8	04-Apr	54326	B	B-303	500	B-302	700					1200
9	05-Apr	54327	C	C-102	1100	C-104	600	C-106	500			2200
10	06-Apr	54328	B	B-301	1200	B-305	1500	B-307	600	B-304	800	4100
11	07-Apr	54329	C	C-102	1100	C-101	600	C-103	500			2200
12		54330
13		54331
14		54332
15		54333
16		54334
17		54335
18		54336
19		54337
20		54338
21		54339
22		54340
23		54341
24		54342
Dispatch Data

Cell Formulas
Range	Formula
L3:L11	L3	=E3+G3+I3+K3

 

[vikash_05]

SUMMARY SHEET FOR SIZE-C

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Batch No	Batch Quantity	Loading-1			Loading-2			Loading-3			Loading-4			Loading-5			Loading-6			Total	Balance
2			Date	Ch No.	Qty	Date	Ch No.	Qty	Date	Ch No.	Qty	Date	Ch No.	Qty	Date	Ch No.	Qty	Date	Ch No.	Qty
3	C-101	15000
4	C-102	20000
5	C-103	21000
6	C-104	18000
7	C-105	19000
8	C-106	15000
9	C-107	16000
10	C-108	17000
11	C-109
12	C-110
13	C-111
14	C-112
15	C-113
16	C-114
17	C-115
18	C-116
19	C-117
20	C-118
21
22
23
24
Size-C

 

[vikash_05]

Sheet Names:
Dispatch Data
Size-A
Size-B
Size-C

uploaded xl2bb data. hope its okay.

 

[DanteAmor]

uploaded xl2bb data. hope its okay.

That's perfect.

You could answer this:
- How many Batch-Qty columns can you have, only 4 or can there be more?
- How many "Size" sheets are you going to have?
- How many "Loading" could a "Batch No" have?
- How many rows will you probably have on the "Dispatch Data" sheet?

 

[DanteAmor]

You could answer this:
- How many Batch-Qty columns can you have, only 4 or can there be more? It doesn't matter it can be much more.
- How many "Size" sheets are you going to have? It doesn't matter it can be much more.
- How many "Loading" could a "Batch No" have? It doesn't matter it can be much more.
- How many rows will you probably have on the "Dispatch Data" sheet? It doesn't matter it can be much more.

With the following macro, it doesn't matter how many columns or rows or sheets.
The name of the sheets must be equal to "Size-" and the letter that is in column C of the sheet "Dispatch Data" ex: "Size-A"

Sub Batch_Summary()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim dic As Object, dic2 As Object, ky As Variant
  Dim i As Long, j As Long, a As Variant, b As Variant, c As Variant
  '
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  '
  Set sh1 = Sheets("Dispatch Data")
  Set dic = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
  '
  a = sh1.Range("A3", sh1.Cells(sh1.Range("A" & Rows.Count).End(3).Row, sh1.Cells(2, Columns.Count).End(1).Column - 1)).Value2
  For i = 1 To UBound(a)
    dic2(a(i, 3)) = Empty
    For j = 4 To UBound(a, 2) Step 2
      dic(a(i, j)) = dic(a(i, j)) & "|" & a(i, 1) & "|" & a(i, 2) & "|" & a(i, j + 1)
    Next
  Next
  
  For Each ky In dic2.keys
    Set sh2 = Sheets("Size-" & ky)
    b = sh2.Range("A3:A" & sh2.Range("A" & Rows.Count).End(3).Row).Value2
    ReDim c(1 To UBound(b), 1 To 1)
    For i = 1 To UBound(b)
      c(i, 1) = Mid(dic(b(i, 1)), 2)
    Next
    With sh2.Range("C3").Resize(UBound(b), 1)
      .Value = c
      .TextToColumns , xlDelimited, , , False, False, False, False, Other:=True, OtherChar:="|"
    End With
  Next
End Sub

 

[vikash_05]

Thank You Sir. Works perfect.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.