Please help me get the output for the given file

Hi guys

I've given layout of an excel file below which has three sheets out of which "invoice" and "packaging" sheets are inputs whilst the "output" sheet contains the desired output I want.

I'm finding it difficult to frame an query for the same - It'll be great if someone can help me on that.

Thanks in Advance.

PACKING SHEET-

Packing List No	Unique ID	Packing Date	Customer Name	Item Name	QTY	Rate	Value	SO No
1	1x	01-04-19	A	x	100	3	300	1
2	1y	01-04-19	A	y	200	6	1200	1
3	2z	01-06-19	B	z	300	5	1500	2
4	2z	01-08-19	B	z	100	5	500	2
5	1x	01-08-19	A	x	100	3	300	1
6	1y	01-08-19	A	y	300	6	1800	1
7	3z	15-09-19	B	z	200	4	800	3
8	3z	30-09-19	B	z	500	4	2000	3
9	4a	17-09-19	C	a	300	7	2100	4
10	4a	29-09-19	C	a	300	7	2100	4

INVOICE SHEET-

Invoice No	Unique ID	Invoice Date	Customer Name	Item Name	QTY	Rate	Value	SO No
1	1x	01-05-19	A	x	80	3	240	1
1	1y	01-05-19	A	y	150	6	900	1
2	2z	01-07-19	B	z	200	5	1000	2
3	1x	01-09-19	A	x	100	3	300	1
4	1y	01-09-19	A	y	350	6	2100	1
5	2z	01-09-19	B	z	150	5	750	2
6	3z	15-09-19	B	z	180	4	720	3
7	3z	01-10-19	B	z	495	4	1980	3
8	4a	30-09-19	C	a	600	7	4200	4

DESIRED OUTPUT-

SO No	Invoice No	Packing List No	Item Name	Invoice Date	Packing Date	Invoiced Quantity
1	1	1	x	01-05-19	01-04-19	80
1	1	1	x	01-09-19	01-08-19	20
1	1	1	x	01-09-19	01-08-19	80
1	1	2	y	01-05-19	01-04-19	150
1	4	2	y	01-09-19	01-08-19	50
1	4	6	y	01-09-19	01-08-19	300
2	2	3	z	01-07-19	01-06-19	200
2	5	3	z	01-09-19	01-06-19	100
2	5	4	z	01-09-19	01-08-19	50
3	6	7	z	15-09-19	15-09-19	180
3	7	7	z	01-10-19	15-09-19	20
3	7	8	Z	01-10-19	30-09-19	475
4	8	9	a	30-09-19	17-09-19	300
4	8	10	a	30-09-19	29-09-19	300

METADATA- SO is Sales Order | The output is based on FIFO Logic | Packaging stage is primary whilst Invoice stage is secondary

Forum Discussion

Please help me get the output for the given file

Share

Resources