How To Split Lines Based on Coverage With Multiple Details

[rajendradk9]

Have data in two sheet (SO & PO) in same Workbook, need to have Consolidated data in Sheet3 or other workbook, below example mentioned for understanding.

 

[awoohaw]

I'm sure the forum would like to attempt this question. And some folks will attempt to find a solution. But, please help the forum help you.
While your image is helpful the forum must recreate your scenario manually, which is time consuming and is subject to typos.
Additionally, you do not say what version of excel you use, please update your profile button details with the excel version you are using in this situation.

The best way to share a mini workbook with the forum is to use the xl2bb add-in (link below). if your data is sensitive, then anonymize it. if you cannot use the add in then post the data in a table.

This seems like a solution that can be found using Power Pivot or Power Query, are you familiar with these?

Thanks in advance.

 

[rajendradk9]

Reco SO & PO.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
6	Sheet1 (Raw Data)					Sheet2 (Raw Data)						Sheet3 (Need As Per Below Table Consolidated)
7
8	Sales Order Data					Purchase Order Data						SO DATA				PO DATA				Remark
9	SO NO	LN NO	Model NO	Qty		PO NO	LN NO	Model NO	Qty	Date		SO NO	LN NO	Model NO	Qty	PO NO	LN NO	Date
10	SO1	1	ABC111	10		PO1	1	ABC444	7	01-06-2023		SO1	1	ABC111	10	PO1	3	01-12-2023		Coverage
11	SO1	2	ABC222	5		PO1	2	ABC555	50	01-12-2024		SO1	2	ABC222	3	PO1	4	31-03-2024		SO line should get split as per PO available Qty in Sequence
12	SO1	3	ABC333	10		PO1	3	ABC111	11	01-12-2023		SO1	2	ABC222	2	PO2	1	15-05-2023		SO line should get split as per PO available Qty in Sequence
13	SO2	1	ABC444	5		PO1	4	ABC222	3	31-03-2024		SO1	3	ABC333	10	PO2	2	07-07-2023		Coverage
14	SO2	2	ABC555	10		PO2	1	ABC222	2	15-05-2023		SO2	1	ABC444	5	PO1	1	01-06-2023		Coverage
15	SO3	1	ABC111	5		PO2	2	ABC333	11	07-07-2023		SO2	2	ABC555	10	PO1	2	01-12-2024		Coverage
16	SO4	1	ABC333	5		PO2	3	ABC111	4	07-07-2023		SO3	1	ABC111	1	PO1	3	01-12-2023		Coverage
17	SO4	2	ABC444	10								SO3	1	ABC111	4	PO2	3	07-07-2023		Coverage
18	SO4	3	ABC555	5								SO4	1	ABC333	1	PO2	2	07-07-2023		SO line should get split as per PO available Qty in Sequence
19												SO4	1	ABC333	4					No Coverage
20												SO4	2	ABC444	2	PO1	1	01-06-2023		SO line should get split as per PO available Qty in Sequence
21												SO4	2	ABC444	7					No Coverage
22												SO4	3	ABC555	5	PO1	2	01-12-2024		Coverage
Sheet1

 

[rajendradk9]

Currently I'm Using Microsoft® Excel® for Microsoft 365 MSO Version 2303 64-bit

 

[rajendradk9]

Looking forward anything solution Power Pivot or Power Query

 

[Peter_SSs]

Looking forward anything solution Power Pivot or Power Query

In that case perhaps you should have posted in the Power Tools forum, so I have moved your thread there.

Currently I'm Using Microsoft® Excel® for Microsoft 365

Please update your Account details with that information (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)