INSRWS

[Xlambda]

INSRWS Inserts blank Rows. Calls ZINS
- can insert 1 or more blank rows, called "gap", after each group of r rows. Gap size argument g
- if g<0 a nr. of abs(g) blank rows is also inserted before 1st row
Note: Could have embedded ZINS function with INSRWS but can be a useful function tool to solve other types of tasks, like inserting text patterns.

=LAMBDA(a, [r], [g],
    LET(
        x, MAX(1, r),
        y, IF(g, ABS(g), 1),
        q, ZINS(ROWS(a), x, y),
        s, IF(g < 0, VSTACK(SEQUENCE(y) ^ 0 - 1, q), q),
        b, INDEX(IF(a = "", "", a), s, SEQUENCE(, COLUMNS(a))),
        IF(s, b, "")
    )
)

ZINS(n,x,g) Zeros Insert, Inserts 1 or more zeros to a sequence of n elements
n: nr. of elements
x: element's group size
g: 0's group size

=LAMBDA(n, x, g,
    LET(
        d, IF(SEQUENCE(x) < x, ",", "," & REPT("0,", g)),
        --TEXTSPLIT(TEXTJOIN(d, , SEQUENCE(n)), , ",")
    )
)

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2						r,g,omit.				r,2, g,omit.				r,omit. ,g,2				r,3,g,2				r,5,g,3				r,omit. ,g,-1				r,4,g,-3
3		a				=INSRWS(B4#)				=INSRWS(B4#,2)				=INSRWS(B4#,,2)				=INSRWS(B4#,3,2)				=INSRWS(B4#,5,3)				=INSRWS(B4#,,-1)				=INSRWS(B4#,4,-3)
4		1	2	3		1	2	3		1	2	3		1	2	3		1	2	3		1	2	3
5		4	5	6						4	5	6						4	5	6		4	5	6		1	2	3
6		7	8	9		4	5	6										7	8	9		7	8	9
7		10	11	12						7	8	9		4	5	6						10	11	12		4	5	6		1	2	3
8		13	14	15		7	8	9		10	11	12										13	14	15						4	5	6
9		16	17	18														10	11	12						7	8	9		7	8	9
10		19	20	21		10	11	12		13	14	15		7	8	9		13	14	15										10	11	12
11		22	23	24						16	17	18						16	17	18						10	11	12
12		25	26	27		13	14	15														16	17	18
13		28	29	30						19	20	21		10	11	12						19	20	21		13	14	15
14		31	32	33		16	17	18		22	23	24						19	20	21		22	23	24						13	14	15
15		34	35	36														22	23	24		25	26	27		16	17	18		16	17	18
16		37	38	39		19	20	21		25	26	27		13	14	15		25	26	27		28	29	30						19	20	21
17		40	41	42						28	29	30														19	20	21		22	23	24
18		43	44	45		22	23	24
19		46	47	48						31	32	33		16	17	18		28	29	30						22	23	24
20		49	50	51		25	26	27		34	35	36						31	32	33		31	32	33
21																		34	35	36		34	35	36		25	26	27		25	26	27
22						28	29	30		37	38	39		19	20	21						37	38	39						28	29	30
23										40	41	42										40	41	42		28	29	30		31	32	33
24						31	32	33										37	38	39		43	44	45						34	35	36
25										43	44	45		22	23	24		40	41	42						31	32	33
26						34	35	36		46	47	48						43	44	45
27																										34	35	36
28						37	38	39		49	50	51		25	26	27						46	47	48						37	38	39
29																		46	47	48		49	50	51		37	38	39		40	41	42
30						40	41	42										49	50	51										43	44	45
31														28	29	30										40	41	42		46	47	48
32						43	44	45
33																										43	44	45
34						46	47	48						31	32	33
35																										46	47	48		49	50	51
36						49	50	51
37														34	35	36										49	50	51
38
39
40														37	38	39
41
42
43														40	41	42
44
45
46														43	44	45
47
48
49														46	47	48
50
51
52														49	50	51
53
Sheet1

Cell Formulas
Range	Formula
F3,J3,N3,R3,V3,Z3,AD3	F3	=FORMULATEXT(F4)
B4:D20	B4	=SEQUENCE(17,3)
F4:H36	F4	=INSRWS(B4#)
J4:L28	J4	=INSRWS(B4#,2)
N4:P52	N4	=INSRWS(B4#,,2)
R4:T30	R4	=INSRWS(B4#,3,2)
V4:X29	V4	=INSRWS(B4#,5,3)
Z4:AB37	Z4	=INSRWS(B4#,,-1)
AD4:AF35	AD4	=INSRWS(B4#,4,-3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:AF53	Expression	=B4<>""	text	NO
B4:AF52	Expression	="b4<>"""""	text	NO
B4:AF52	Expression	="B4<>"""""	text	NO

 

[Xlambda]

STKCTY(tb)

=LAMBDA(tb,
    LET(
        c, TAKE(tb, 1),
        cy, FILTER(c, c <> ""),
        k, ASTACK(DROP(tb, 2), 2),
        f, TAKE(k, , 1),
        i, QUOTIENT(SEQUENCE(ROWS(k)) - 1, ROWS(tb) - 2) + 1,
        h, HSTACK(INDEX(cy, i), k),
        l, FILTER(h, f <> ""),
        VSTACK({"City", "Name", "Age"}, l)
    )
)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2			Mumbai		New York		Hong Kong		London		Mumbai		New York		Hong Kong		London		Madrid
3		Name	Age	Name	Age	Name	Age	Name	Age		Name	Age	Name	Age	Name	Age	Name	Age	Name	Age
4		A	14	E	14	I	14	N	5		A	14	E	14	I	14	N	5	R	23
5		B	12	F	12	J	12	O	5		B	12	F	12	J	12	O	5	S	32
6		c	18	G	18	K	18	p	5		c	18	G	18	K	18	p	5	T	43
7		D	10			L	19	Q	5		D	10			L	19	Q	5	U	18
8						M	20								M	20			V	12
9															MA	17			X	23
10															MB	20
11
12		=STKCTY(B2:I8)				=STKCTY(K2:T10)
13		City	Name	Age		City	Name	Age
14		Mumbai	A	14		Mumbai	A	14
15		Mumbai	B	12		Mumbai	B	12
16		Mumbai	c	18		Mumbai	c	18
17		Mumbai	D	10		Mumbai	D	10
18		New York	E	14		New York	E	14
19		New York	F	12		New York	F	12
20		New York	G	18		New York	G	18
21		Hong Kong	I	14		Hong Kong	I	14
22		Hong Kong	J	12		Hong Kong	J	12
23		Hong Kong	K	18		Hong Kong	K	18
24		Hong Kong	L	19		Hong Kong	L	19
25		Hong Kong	M	20		Hong Kong	M	20
26		London	N	5		Hong Kong	MA	17
27		London	O	5		Hong Kong	MB	20
28		London	p	5		London	N	5
29		London	Q	5		London	O	5
30						London	p	5
31						London	Q	5
32						Madrid	R	23
33						Madrid	S	32
34						Madrid	T	43
35						Madrid	U	18
36						Madrid	V	12
37						Madrid	X	23
38
Sheet19

Cell Formulas
Range	Formula
B12,F12	B12	=FORMULATEXT(B13)
B13:D29	B13	=STKCTY(B2:I8)
F13:H37	F13	=STKCTY(K2:T10)
Dynamic array formulas.

 

[st001]

Amazing!
I wouldn't have thought of using the QUOTIENT function in that way to get the index values. (I'd have probably used REPT like you did in the your 2nd to last version)
The ASTACK function also really helps.
I guess you can make this generic and fully dynamic to any kind of unpivot and stack operation, for a table of repeating pattern of columns of any width, and pass in the "City" text value as a parameter too.
Thanks again for solving this challenge!