How to tabulate text file with data shown in blocks?

[Fractalis]

Good day,

I have an input file like this below in range A1:A98 (the actual data could have more than 5000 lines), for which I'd like to tabulate the info in the way I show in table (the numbers in red are only filled down from previous above). The parameters like Az to Gz are words with different length in actual file, here are smaller for example purposes.

Is there a way to do it with Excel formulas or only with VBA macro? Thanks for any help.

Sample File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	SOME TEXT SOME TEXT SOME TEXT		Az	Bz	Cz	Dz	Ez	Fz	Gz	DRM	RRCO	RDX	FCD	MBZ	HGW
2	SOME TEXT		1	5	3	0	0
3	RETCODE = 0		1	5	3	4	1		1
4			3	2	8	6	9	3
5	Az = 1		7	2	2	9	0
6	Bz = 5		7	2	2	1	4
7	Cz = 3		7	2	2	7	0	1
8	Dz = 0		7	2	2	1			8
9	Ez = 0			2	MAIN-332					CTRRES	DLAM	PRM	<NULL>	XJN	bbv.uj002.pkl706.svfdata
10				2	MAIN-332					CTRRES	DLAM	PRM	ALLO	STT	bbv.uj003.pkl706.svfdata
11	Dz = 4			2	MAIN-332					CTRRES	DLAM	PRM	<NULL>	BNN	bbv.uj410.pkl310.svfdata
12	Ez = 1			5	GOPAG					BRI5T	TALD	Rdx	Fcd	Mbz	bbv.123k.svfdata
13	Gz = 1			5	GOPAG					BRI5T	TALD	REJ	PRMRJC	YW	bbv.322h7.svfdata
14				5	GOPAG					BRI5T	TALD	PRM	<NULL>	G4	bbv.119.svfdata
15
16	blah blah
17
18
19	--- ENDBLOCK
20
21	SOME TEXT
22	RETCODE = 0
23	Az = 3
24	Bz = 2
25	Cz = 8
26	Dz = 6
27	Ez = 9
28	Fz = 3
29
30
31
32	blah blah
33
34	blah blah
35
36	--- ENDBLOCK
37
38	SOME TEXT
39	RETCODE = 0
40	Az = 7
41	Bz = 2
42	Cz = 2
43	Dz = 9
44	Ez = 0
45
46	Dz = 1
47	Ez = 4
48
49	Dz = 7
50	Ez = 0
51	Fz = 1
52
53	Dz = 1
54	Gz = 8
55
56
57	blah blah
58
59	blah blah
60
61	--- ENDBLOCK
62	%%XYZ HGGYT:%%
63
64	BLA BLAH = 0
65
66	Bz = 2
67	Cz = MAIN-332
68	DRM = CTRRES
69	RRCO = DLAM
70
71	blah blahblah blahblah blahblah blah
72	-----------------------------------------------
73
74	RDX FCD MBZ HGW
75	PRM <NULL> XJN bbv.uj002.pkl706.svfdata
76	PRM ALLO STT bbv.uj003.pkl706.svfdata
77	PRM <NULL> BNN bbv.uj410.pkl310.svfdata
78
79
80	blah blah
81
82	--- ENDBLOCK
83	%%XYZ HGGYT:%%
84
85	BLA BLAH = 0
86
87	Bz = 5
88	Cz = GOPAG
89	DRM = BRI5T
90	RRCO = TALD
91
92	blah blahblah blahblah blahblah blah
93	-----------------------------------------------
94
95	RDX FCD MBZ HGW
96	Rdx Fcd Mbz bbv.123k.svfdata
97	REJ PRMRJC YW bbv.322h7.svfdata
98	PRM <NULL> G4 bbv.119.svfdata
SM

 

[jdellasala]

Bring the text file into Power Query, manipulate it there, and output a nice, clean table. Looks like it's Tab Delimited.

 

[Fractalis]

Bring the text file into Power Query, manipulate it there, and output a nice, clean table. Looks like it's Tab Delimited.

Thanks for answer, but I know very few about power query. Actually I'm working in Excel 2016

 

[Fractalis]

Someone could help me with a formula to tabulate at least data for columns with green header?