Macro help to transpose data in stacked form

[Fractalis]

Hi all,

I
May someone help with with this.

I'd like to tabulate this stacked structure of parameters (column A) and values(column B) that has multiple blocks as shown below
in range A1:B3 in the way shown in D1:L14.

First block of data (first row to be tabulated), begins with "WORLD" and begins with "CAPITAL". Then could continue 0 o more blocks containing
only a few parameters, that begins with "COUNTRYCODE" and ends with "CAPITAL". Then could be others blocks that begins with "CONTINENT"
and ends with "PRM". Then could be others blocks that contains only "PRM".

Thee headers in output would be the unique parameters from column A, except "POLCODE" that is not needed to print in output. Each block that
begins with "WORLD" or "COUNTRYCODE" would be a new row in output. For "PRM" values extract the numbers after "ncc" and after "kng".

How to know if "COUNTRYCODE" belongs or not to a bigger block that begins with "WORLD"? Well, one row only could have the value of each
parameter. If one parameter appears again, should go in another row. The same applies to "PRM".

Thanks in advance for any help

COUNTRIES.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	WORLD	1		WORLD	CONTINENT	CONTINENTNAME	CONTINENTCODE	GOVERNMENT	COUNTRYCODE	COUNTRY	CAPITAL	PRM
2	CONTINENT	1		1	1	AMERICA	AME01	PRE	55	BRAZIL	BRASILIA
3	CONTINENTNAME	AMERICA		1	1	AMERICA	AME01	PRE	51	PERU	LIMA
4	CONTINENTCODE	AME01		1	1	AMERICA	AME01	PRE	1784	SAINT_VINCENT	KINGSTOWN
5	GOVERNMENT	PRE		1	3	EUROPE	EUR03	KNGD	359	BULGARIA	SOFIA
6	COUNTRYCODE	55		1	3	EUROPE	EUR03	KNGD	36	HUNGARY	BUDAPEST
7	COUNTRY	BRAZIL			2	ASIA	EXC35	KNGD				060-458
8	CAPITAL	BRASILIA			2	ASIA	EXC35	KNGD				392-392
9	COUNTRYCODE	51			5	AFRICA	EXC35	PRE				231-251
10	COUNTRY	PERU			5	AFRICA	EXC35	PRE				324-224
11	CAPITAL	LIMA			5	AFRICA	EXC35	PRE				450-261
12	COUNTRYCODE	1784			5	AFRICA	EXC35	PRE				686-221
13	COUNTRY	SAINT_VINCENT			5	AFRICA	EXC35	PRE				788-216
14	CAPITAL	KINGSTOWN			5	AFRICA	EXC35	PRE				148-235
15	WORLD	1
16	CONTINENT	3
17	CONTINENTNAME	EUROPE
18	CONTINENTCODE	EUR03
19	GOVERNMENT	KNGD
20	COUNTRYCODE	359
21	COUNTRY	BULGARIA
22	CAPITAL	SOFIA
23	COUNTRYCODE	36
24	COUNTRY	HUNGARY
25	CAPITAL	BUDAPEST
26	CONTINENT	2
27	CONTINENTNAME	ASIA
28	CONTINENTCODE	EXC35
29	GOVERNMENT	KNGD
30	POLCODE	ASIRPL2
31	PRM	kng.cc060.ncc458.svfdata
32	PRM	kng.cc392.ncc392.svfdata
33	CONTINENT	5
34	CONTINENTNAME	AFRICA
35	CONTINENTCODE	EXC35
36	GOVERNMENT	PRE
37	POLCODE	AFRRPL5
38	PRM	pre.cc231.ncc251.svfdata
39	PRM	pre.cc324.ncc224.svfdata
40	PRM	pre.cc450.ncc261.svfdata
41	PRM	pre.cc686.ncc221.svfdata
42	PRM	pre.cc788.ncc216.svfdata
43	PRM	pre.cc148.ncc235.svfdata
Sheet7

 

[Fractalis]

I used in B2 a formula that is slightly different than yours:

=IFERROR(FILTERXML($A$1,"//COUNTRIES/group["&ROW(A1)&"]/"&B$1),"")

Excellent!!! It works better and without to insert an array formula like mine. I think the fill-down thing is not possible but only with a macro? or is do you still have some trick hidden?

 

[Anthony47]

Office 365 and 2021 have a feature named Dynamic Array that would do the job.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fractalis]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done. Using Excel 2019