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.

 

[DanteAmor]

Check if the following array formulas is what you need.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Dispatch Data
2	Date	Challan	Batch	Qty	Batch	Qty
3	01-abr	54321	c-129	2000	c-128	2500
4	01-abr	54322	c-128	1000	c-129	1300
5	02-abr	54323	c-128	600
6	03-abr	54324	c-130	1000	c-131	1200
7	04-abr	54325	c-132	200	c-131	500
8	04-abr	54326	c-133	500	c-130	700
9	05-abr	54327	c-131	1100	c-132	600
10
11
12
13	Batch Summary
14	Batch	Loading 1			Loading 2			Loading 3
15		Date	Challan	Qty	Date	Challan	Qty	Date	Challan	Qty
16	c-128	01-abr	54321	2500	01-abr	54322	1000	02-abr	54323	600
17	c-129	01-abr	54321	2000	01-abr	54322	1300
18	c-130	03-abr	54324	1000	04-abr	54326	700
19	c-131	03-abr	54324	1200	04-abr	54325	500	05-abr	54327	1100
20	c-132	04-abr	54325	200	05-abr	54327	600
21	c-133	04-abr	54326	500
Hoja11

Cell Formulas
Range	Formula
B16:B21,H16:H21,E16:E21	B16	=IFERROR(INDIRECT(TEXT(SMALL(IF($A$3:$F$9=$A16,ROW($A$3:$F$9)*1000+1),INT(COLUMNS($A1:C1)/3)),"R000C000"),0),"")
C16:C21,I16:I21,F16:F21	C16	=IFERROR(INDIRECT(TEXT(SMALL(IF($A$3:$F$9=$A16,ROW($A$3:$F$9)*1000+2),INT(COLUMNS($A1:D1)/3)),"R000C000"),0),"")
D16:D21,J16:J21,G16:G21	D16	=IFERROR(INDIRECT(TEXT(SMALL(IF($A$3:$F$9=$A16,ROW($A$3:$F$9)*1000+COLUMN($A$3:$F$9)+1),INT(COLUMNS($A1:D1)/3)),"R000C000"),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[vikash_05]

Sir thanks a lot. It works great. Just one more thing. I am using two different sheets. one for dispatch details and and other for summary. In that case the formula is not returning the value.

 

[vikash_05]

Thank You so much. Got it working.

 

[DanteAmor]

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

 

[vikash_05]

Sir, the formula is badly slowing down the excel. I had put only about 100 entries, 1000 more to go. Any other option Plz.

 

[DanteAmor]

Another option is to use a macro.
If you want, I will prepare the macro for you?

 

[vikash_05]

Yes please. Thank you

 

[DanteAmor]

You can comment:
- The name of the 2 sheets.
- In which cell the data start on the first sheet.
- In which cell the data start on the second sheet.

 

[vikash_05]

My actual data is like this.