Restructure data table in specific upload format

[vaibhav106]

Hello experts,
I do a daily routine to convert a data table in a specific format which can be than use to upload that data in ERP. I know that Excel macros could be great help here. I have tried to record macro for this process but it doesn't work as per expectation.

I request you to help me here. Can you give me a macro code which can be used to convert data as per expected output. I am sharing source file & expected output file for your easy understanding. This a entry data with header & line record which should be uploaded in ERP system.

Source Table: -

	JRNCOMPANYCODE	JRNDOCUMENTTYPE	JRNDOCUMENTDATE	JRNPOSTINGDATE	JRNREFERENCE	JRNDOCUMENTHEADERTEXT	JRNCURRENCY	JRNEXCHANGERATE	JRNLPOSTINGKEY	JRNLACCOUNT	JRNLAMOUNT	JRNLSPECIALINDICATOR	JRNLTAXCODE	JRNLASSIGNMENT	JRNLLONGTEXT	JRNLCOSTCENTER	JRNLINTERNALORDER	JRNLBASELINEDATE	JRNLTERMSOFPAYMENT	JRNLPROFITCENTER	JRNLREFERENCEKEY1	JRNLREFERENCEKEY2	JRNLREFERENCEKEY3	JRNLBUSINESSPLACE	JRNLSECTIONCODE	JRNLSEGMENT	JRNLHSNSACCODE	JRNLCREDITCONTROLAREA
Record Type	Company Code*	Document Type*	Document Date*	Posting Date*	Reference*	Document Header Text*	Currency*	Exchange rate	Posting Key*	Account*	Amount*	Special G/L ind.	Tax code	Assignment*	Item Text*	Cost Center	Internal Order	Baseline Payment Date	Terms of Payment	Profit Center*	Reference Key 1	Reference Key 2	Reference Key 3	Business Place	Section Code	Segment	HSN/SAC Code	Credit Control Area
1	1000	B1	06.12.2022	31.12.2022	MH3713300564	1800000533	INR		40	5010216	5,46,120.34			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
1	1000	B1	06.12.2022	31.12.2022	MH3713300564	1800000533	INR		40	2340449	98,301.66			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
1	1000	B1	06.12.2022	31.12.2022	MH3713300564	1800000533	INR		31	PLNTCP08	6,44,422.00			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
2	1000	B1	06.12.2022	31.12.2022	MH3713300565	1800000534	INR		40	5010216	1,09,223.73			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
2	1000	B1	06.12.2022	31.12.2022	MH3713300565	1800000534	INR		40	2340449	19,660.27			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
2	1000	B1	06.12.2022	31.12.2022	MH3713300565	1800000534	INR		31	PLNTCP08	1,28,884.00			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
20	1000	B1	31.12.2022	31.12.2022	MH3713300614	1800000582	INR		40	5010216	2,82,755.00			PLNTCP08	1800000582	10MI104401				10MI104				MP02	MP02	CORPORATE
20	1000	B1	31.12.2022	31.12.2022	MH3713300614	1800000582	INR		31	PLNTCP08	2,82,755.00			PLNTCP08	1800000582	10MI104401				10MI104				MP02	MP02	CORPORATE
21	1000	B1	31.12.2022	31.12.2022	MH3713300641	1800000606	INR		40	5010216	4,339.84			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE
21	1000	B1	31.12.2022	31.12.2022	MH3713300641	1800000606	INR		40	2340449	781.17			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE
21	1000	B1	31.12.2022	31.12.2022	MH3713300641	1800000606	INR		31	PLNTCP08	5,121.01			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE

Expected output: -

	JRNCOMPANYCODE	JRNDOCUMENTTYPE	JRNDOCUMENTDATE	JRNPOSTINGDATE	JRNREFERENCE	JRNDOCUMENTHEADERTEXT	JRNCURRENCY	JRNEXCHANGERATE	JRNLPOSTINGKEY	JRNLACCOUNT	JRNLAMOUNT	JRNLSPECIALINDICATOR	JRNLTAXCODE	JRNLASSIGNMENT	JRNLLONGTEXT	JRNLCOSTCENTER	JRNLINTERNALORDER	JRNLBASELINEDATE	JRNLTERMSOFPAYMENT	JRNLPROFITCENTER	JRNLREFERENCEKEY1	JRNLREFERENCEKEY2	JRNLREFERENCEKEY3	JRNLBUSINESSPLACE	JRNLSECTIONCODE	JRNLSEGMENT	JRNLHSNSACCODE	JRNLCREDITCONTROLAREA
Record Type	Company Code*	Document Type*	Document Date*	Posting Date*	Reference*	Document Header Text*	Currency*	Exchange rate	Posting Key*	Account*	Amount*	Special G/L ind.	Tax code	Assignment*	Item Text*	Cost Center	Internal Order	Baseline Payment Date	Terms of Payment	Profit Center*	Reference Key 1	Reference Key 2	Reference Key 3	Business Place	Section Code	Segment	HSN/SAC Code	Credit Control Area
H	1000	B1	06/12/2022	31/12/2022	MH3713300564	1800000533	INR
L									40	5010216	5,46,120.34			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
L									40	2340449	98,301.66			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
L									31	PLNTCP08	6,44,422.00			PLNTCP08	1800000533	10LBD10001				10LBD01				HP01	HP01	CORPORATE
H	1000	B1	06/12/2022	31/12/2022	MH3713300565	1800000534	INR
L									40	5010216	1,09,223.73			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
L									40	2340449	19,660.27			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
L									31	PLNTCP08	1,28,884.00			PLNTCP08	1800000534	10LBD10001				10LBD01				HP01	HP01	CORPORATE
H	1000	B1	31/12/2022	31/12/2022	MH3713300614	1800000582	INR
L									40	5010216	2,82,755.00			PLNTCP08	1800000582	10MI104401				10MI104				MP02	MP02	CORPORATE
L									31	PLNTCP08	2,82,755.00			PLNTCP08	1800000582	10MI104401				10MI104				MP02	MP02	CORPORATE
H	1000	B1	31/12/2022	31/12/2022	MH3713300641	1800000606	INR
L									40	5010216	4,339.84			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE
L									40	2340449	781.17			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE
L									31	PLNTCP08	5,121.01			PLNTCP08	1800000606	10MP305501				10MP305				MH01	MH01	CORPORATE

 

[vaibhav106]

Hi Experts,
Please help me.