Best way to automate the reformatting of this?

[FaxMeBeer]

I don't know how to best deal with getting my example onto this post, so forgive the copy pasta. Below is an example of how my company's system exports reports in Excel (there is no CSV, or un-formatted option for export).

So, the employee's name is sort of randomly inserted at the top of the data related to that employee. Then there are all the blank columns, headers are not properly aligned to the columns they represent. and it's just a mess. I have tried recording a macro of the cleanup, but since the data changes (the report has more records each time) that doesn't work. So, I'm going to have to do something else (VBA, Power Query...whatever), and no matter what I use, I'm going to have to learn it. What solution would be best for me to invest the time in learning, since I'm going to have to learn something?

												Closed Claims By Adjuster
REPORT CRITERIA: Closed Date: 01/01/2019 - 04/17/2019
Claim #		GBL / Reg No.		Company	ST		Claimant Name			Claimant E-mail		Amount	Amount	Var %	Charge	Claim Date	Closed Date	Date Settled	Days	Claim Type	Claim Status
												Claimed	Paid	Back
John Johnson
696457	BGAC0414608	AMWD	060	John Smith			Example@example.com		$1,800.00	$562.48	0.31	$87.50	01/26/2018	03/07/2019	03/07/2019	405	Cargo	Completed
703015	AGFM0368441	FOFD	209	John Smith			Example@example.com		$1,500.00	$0.00	0.00	$0.00	06/26/2018	03/04/2019	03/04/2019	251	Cargo	Completed
703080	CNNQ0494844	DMS	094	John Smith			Example@example.com		$5,527.99	$2,722.99	0.49	$500.00	07/02/2018	01/31/2019	01/31/2019	213	Cargo	Completed
680639	KKFA0555596	DMS	067	John Smith			Example@example.com		$39,843.00	$0.00	0.00	$0.00	07/13/2018	04/10/2019	04/10/2019	271	Loss/Damage	File 13
680639	KKFA0555596	DMS	067	John Smith			Example@example.com		$39,907.00	$245.00	0.01	$0.00	07/25/2018	04/10/2019	04/10/2019	259	Loss/Damage	File 13
705056	WKAS0160382	CRWV31	004	John Smith			Example@example.com		$4,547.00	$1,516.79	0.33	$500.00	08/13/2018	01/07/2019	01/07/2019	147	Cargo	Completed
706016	BGAC0431018	DMS	087	John Smith			Example@example.com		$5,440.00	$2,561.60	0.47	$500.00	08/14/2018	01/10/2019	01/10/2019	149	Cargo	Completed
706664	BKAS0097794	DMS	074	John Smith			Example@example.com		$4,395.96	$1,703.99	0.39	$497.50	08/21/2018	01/28/2019	01/28/2019	160	Cargo	Completed
707106	LKNQ0329379	DMS	095	John Smith			Example@example.com		$3,569.00	$784.50	0.22	$147.50	08/23/2018	02/04/2019	02/04/2019	165	Cargo	Completed
711009	HBAT0075292	CRWV31	030	John Smith			Example@example.com		$4,900.00	$2,438.54	0.50	$350.55	08/24/2018	01/30/2019	01/30/2019	159	Cargo	Completed
706474	BGAC0432355	CRWV31	030	John Smith			Example@example.com		$4,975.32	$3,941.94	0.79	$987.97	08/24/2018	02/06/2019	02/06/2019	166	Cargo	Completed
705458	CNNQ0513999	CRWV31	004	John Smith			Example@example.com		$19,410.42	$8,347.55	0.43	$6,159.13	08/30/2018	04/12/2019	04/12/2019	225	Cargo	Completed
712913	BGAC0440734	DMS	081	John Smith			Example@example.com		$4,260.00	$991.18	0.23	$406.25	09/11/2018	02/01/2019	02/01/2019	143	Cargo	Completed
707887	HAFC0473002	CRWV31	030	John Smith			Example@example.com		$1,990.00	$1,755.00	0.88	$325.00	09/12/2018	01/10/2019	01/10/2019	120	Cargo	Completed
708413	BGAC0435209	CRWV31	030	John Smith			Example@example.com		$3,105.00	$3,105.00	1.00	$3,105.00	03/01/2019	04/12/2019	04/12/2019	42	Demand on Carrier	Completed
710279	BGAC0437638	CRWV31	030	John Smith			Example@example.com		$1,150.00	$0.00	0.00	$0.00	03/01/2019	03/15/2019	03/15/2019	14	Cargo	Denied
682370	CNNQ0471978	FOFD	201	John Smith			Example@example.com		$1,876.99	$1,876.99	1.00	$500.00	03/25/2019	04/16/2019	04/16/2019	22	Demand on Carrier	Completed
679700	BGAC0394813	CRWV31	030	John Smith			Example@example.com		$142.25	$142.25	1.00	$0.00	03/25/2019	04/11/2019	04/11/2019	17	Demand on Carrier	Completed
710205	WKFS0078155	CRWV31	004	John Smith			Example@example.com		$2,548.24	$2,548.00	1.00	$2,548.00	03/25/2019	04/16/2019	04/16/2019	22	Demand on Carrier	Completed
710736	HBAT0075215	CRWV31	030	John Smith			Example@example.com		$2,670.00	$2,670.00	1.00	$500.00	03/27/2019	04/17/2019	04/17/2019	21	Demand on Carrier	Completed
Total For Adjuster
				Total Amount Claimed				$487,174.16
				Total Amount Paid					$146,246.91
				Variation Amt Paid VS Amt Claimed			340,927.25
				Total Amount Charge Back			$52,503.88
				Average Days to Settle				0
				Total Number of Claims				101
				Average Paid per Claim				1,447.99
Don Smith
709177	CNNQ0519154	DMS	094	John Smith			Example@example.com		$31,590.16	$21,210.68	0.67	$19,294.02	08/28/2018	01/25/2019	01/25/2019	150	Loss/Damage	Completed
710043	BGAC0437415	DMS	094	John Smith			Example@example.com		$47,397.53	$54,439.17	1.15	$36,844.44	08/29/2018	01/16/2019	01/16/2019	140	Remediation	Completed
710588	HAFC0476057	FOFD	201	John Smith			Example@example.com		$2,499.00	$1,027.56	0.41	$375.00	09/04/2018	01/09/2019	01/09/2019	127	Cargo	Completed
703570	WKAS0159370	CRWV31	004	John Smith			Example@example.com		$6,015.00	$1,989.48	0.33	$500.00	09/10/2018	01/03/2019	01/03/2019	115	Cargo	Completed
710509	HBAT0075150	CRWV31	030	John Smith			Example@example.com		$2,505.00	$1,751.95	0.70	$763.50	09/12/2018	01/10/2019	01/10/2019	120	Cargo	Completed
705362	BGAC0429753	CRWV31	004	John Smith			Example@example.com		$6,884.00	$3,900.58	0.57	$500.00	09/17/2018	02/08/2019	02/08/2019	144	Cargo	Completed
706200	CLPK0017398	AMWD	061	John Smith			Example@example.com		$1,320.00	$335.92	0.25	$325.93	09/28/2018	01/28/2019	01/28/2019	122	Cargo	Completed
712900	BGAC0440747	FOFD	201	John Smith			Example@example.com		$5,334.00	$1,054.83	0.20	$850.23	10/08/2018	03/04/2019	03/04/2019	147	Cargo	Completed
709252	BGAC0435953	CRWV31	030	John Smith			Example@example.com		$10,967.39	$6,679.38	0.61	$2,396.93	10/15/2018	01/22/2019	01/22/2019	99	Cargo	Completed
709071	LKNQ0331307	DMS	087	John Smith			Example@example.com		$850.00	$399.00	0.47	$162.50	10/15/2018	01/02/2019	01/02/2019	79	Cargo	Completed
710625	KKFA0664067	CRWV31	030	John Smith			Example@example.com		$1,239.83	$1,389.83	1.12	$0.00	10/16/2018	03/04/2019	03/04/2019	139	Cargo	Completed
712366	AGFM0385865	AMWD	061	John Smith			Example@example.com		$275.00	$242.98	0.88	$242.98	10/22/2018	01/17/2019	01/17/2019	87	Cargo	Completed
708781	BGAC0435591	DMS	074	John Smith			Example@example.com		$2,490.00	$1,552.43	0.62	$953.13	10/22/2018	01/28/2019	01/28/2019	98	Cargo	Completed
711162	MLNQ0532319	CRWV31	004	John Smith			Example@example.com		$2,455.00	$1,154.57	0.47	$486.28	10/22/2018	01/16/2019	01/16/2019	86	Cargo	Completed
695307	AGFM0340698	CRWV31	004	John Smith			Example@example.com		$2,150.00	$0.00	0.00	$0.00	02/11/2019	02/11/2019	02/11/2019	0	Cargo	Denied
725510	BGAC0452576	CRWV31	030	John Smith			Example@example.com		$1,000.00	$0.00	0.00	$0.00	02/12/2019	02/12/2019	02/12/2019	0	Remediation	Completed
707778	CNNQ0517260	DMS	093	John Smith			Example@example.com		$2,150.34	$0.00	0.00	$0.00	02/14/2019	02/26/2019	02/26/2019	12	Cargo	Denied
709743	CNNQ0520109	DMS	075	John Smith			Example@example.com		$3,458.96	$0.00	0.00	$0.00	02/14/2019	03/07/2019	03/07/2019	21	Loss/Damage	File 13
725237	WKFS0081938	CRWV31	004	John Smith			Example@example.com		$699.00	$699.00	1.00	$0.00	02/14/2019	02/14/2019	02/14/2019	0	Loss/Damage	Completed

 

[GraH]

My 5 cent, PQ has a much steeper learning curve over VBA.