Covert some rows to header but some others as data below relative header

[Yuppiedad]

Hi,

I receive monthly statement from vendor after providing services. However it comes in as en email message. Each transaction is separated by dotted lines. The content likes below:

Repairs completed from 05/04/2018 - 08/02/2018
Repairs receiving labor reimbursement:
---------------------------------------------------------------------------------------
Repair # PO# Dispatch # Serial #
030320016811 W180508060(N) G319337548 G6TVK340JCL7
Repair Date SN Type Warranty
05/09/2018 AASP/Carrier Facilit EXPENSED LIMITED WARRANTY
Material # Description Amount Score Total
011-0636 FACILITATION,WARRANTY REIMBURSEMENT/REP 20.50 1.65 33.83
---------------------------------------------------------------------------------------
Repair # PO# Dispatch # Serial #
030322374722 W180524019(B) G321609416 FM5V2065GWL8
Repair Date SN Type Warranty
05/28/2018 AASP/Carrier Facilit APPLE LIMITED WARRANTY
Material # Description Amount Score Total
011-00008 REIMBURSEMENT,MAIL-IN FACILITATION,BEATS 15.00 N/A 15.00
---------------------------------------------------------------------------------------
Repair # PO# Dispatch # Serial #
030322644170 W180529086(XN) G321874117 FYNV110DHG6W
Repair Date SN Type Warranty
05/30/2018 AASP/Carrier Facilit APPLECARE PROTECTION PLAN
Material # Description Amount Score Total
011-0636 FACILITATION,WARRANTY REIMBURSEMENT/REP 20.50 1.65 33.83
---------------------------------------------------------------------------------------

I copied all of them to Excel worksheet as attached. However all fields are in single column. The actual information should be read as below if I can split them into different columns.

REPAIR#	PO#	DISPATCH#	SERIAL#
030320016811	W180508060(N)	G319337548	G6TVK340JCL7
REPAIRDATE	SNTYPE	WARRANTY
05/09/2018	AASP/CARRIER FACILIT	EXPENSED LIMITED WARRANTY
MATERIAL#	DESCRIPTION	AMOUNT	SCORE	TOTAL
011-0636	FACILITATION,WARRANTY REIMBURSEMENT/REP	20.50	1.65	33.83

Information spread not only in columns but rows as well. The final result I want to be like this:

REPAIR#	PO#	DISPATCH#	SERIAL#	REPAIRDATE	SNTYPE	WARRANTY	MATERIAL#	DESCRIPTION	AMOUNT	SCORE	TOTAL
030320016811	W180508060(N)	G319337548	G6TVK340JCL7	05/09/2018	AASP/CARRIER FACILIT	EXPENSED LIMITED WARRANTY	011-0636	FACILITATION,WARRANTY REIMBURSEMENT/REP	20.50	1.65	33.83
030322374722	W180524019(B)	G321609416	FM5V2065GWL8	05/28/2018	AASP/CARRIER FACILIT	LIMITED WARRANTY	011-00008	REIMBURSEMENT,MAIL-IN FACILITATION,BEATS	15.00	N/A	15.00
030322644170	W180529086(XN)	G321874117	FYNV110DHG6W	05/30/2018	AASP/CARRIER FACILIT	PROTECTION PLAN	011-0636	FACILITATION,WARRANTY REIMBURSEMENT/REP	20.50	1.65	33.83

I really do not know where to start. Please advise.

Thanks a lot,

Waichung

 

[Bill Szysz]

This can't be done by UI.
You should have some knowledge about M.
In the attachment, my solution.

 

[Yuppiedad]

Hi Bill,

Thanks for your help. It works for most records until there are more than one entry under one field. Some records have 2-3 entries of Material# under same Repair#. It generates errors instead. Is it possible to cater multiple entries?

Repair # PO# Dispatch # Serial #
030328933059 W180721004(M) G328251488 G6TWG6CXJCLF

Repair Date SN Type Warranty
07/21/2018 AASP/Carrier Facilit EXPENSED LIMITED WARRANTY

Material # Description Amount Score Total
011-00016 SVC, LABOR TIER RMB4 (LOANER) 15.00 N/A 15.00
011-0636 FACILITATION,WARRANTY REIMBURSEMENT/REP 20.50 1.65 33.83

Thanks,

 

[Bill Szysz]

Check, this one. Maybe it is waht you need.

Regards