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]

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		NARRINS with arrays different dimentions
2				1				2				3				4			=NARRINS(,,B3:D10,F3:H6,J3:L6,N3:P18)
3		A	31	91		B	43	91		C	59	38		D	18	12			A	31	91
4		A	10	87		B	95	91		C	30	53		D	88	69			D	18	12
5		A	13	68		B	95	48		C	73	22		D	76	79			A	10	87
6		A	40	58		B	40	89		C	38	62		D	42	15			D	88	69
7		A	53	37										D	56	71			B	43	91
8		A	55	89										D	54	73			D	76	79
9		A	30	14										D	58	53			C	59	38
10		A	33	11										D	72	63			D	42	15
11														D	35	82			A	13	68
12														D	44	91			D	56	71
13														D	33	43			A	40	58
14														D	73	30			D	54	73
15														D	61	53			B	95	91
16														D	68	32			D	58	53
17														D	68	66			C	30	53
18														D	26	42			D	72	63
19																			A	53	37
20																			D	35	82
21		Note: In iterative process, for consistent results counta(x(i-1))/counta(x(i)) = integer																	A	55	89
22																			D	44	91
23																			B	95	48
24																			D	33	43
25																			C	73	22
26																			D	73	30
27																			A	30	14
28																			D	61	53
29																			A	33	11
30																			D	68	32
31																			B	40	89
32																			D	68	66
33																			C	38	62
34																			D	26	42
35
Sheet10

Cell Formulas
Range	Formula
S2	S2	=FORMULATEXT(S3)
S3:U34	S3	=NARRINS(,,B3:D10,F3:H6,J3:L6,N3:P18)
Dynamic array formulas.

 

[Xlambda]

Introducing ARRAYS , a composed lambda helper function that applies any given function to each element of a sequence of arrays (min 2, max 5) and stacks the results horizontally or vertically.
ARRAYS(a,b,[c],[d],[e])(fn,[hz])
a,b,c,d,e: arrays or single values: min 2 arrays, max 5 arrays
fn: lambda helper function
[hz]: if 1 results stacked horizontally, if omitted vertically

=LAMBDA(a, b, [c], [d], [e],
    LAMBDA(fn, [hz],
        LET(
            x, LAMBDA(i, LET(w, SWITCH(i, 1, a, 2, b, 3, c, 4, d, 5, e), IF(w = "", "", w))),
            o, --VSTACK(ISOMITTED(c), ISOMITTED(d), ISOMITTED(e)),
            na, IFNA(XMATCH(1, o), 4) + 1,
            r, REDUCE(0, SEQUENCE(na), LAMBDA(v, i, IF(hz, HSTACK(v, fn(x(i))), VSTACK(v, fn(x(i)))))),
            IFNA(IF(hz, DROP(r, , 1), DROP(r, 1)), "")
        )
    )
)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		single argument functions									1				3
3											c	d	c		7	7	a	b	a	3
4	1	1									a	a	c					5
5					=ARRAYS(B4,B6,B8,B10,B12:C13)(LAMBDA(x,TYPE(x)))						2				4			xx	xy
6	2	text			1						4	7	4		1	2		yx	xy
7					2						3	3	4		3	1		yy	yx
8	3	TRUE			4						4	2	7					xx	xy
9					16
10	4	#N/A			64						sort unique for each array (results stacked horizontally
11
12	5	a	2								=ARRAYS(K3:M4,K6:M8,O3:T3,O6:P7,R5:S8)(LAMBDA(x,SORT(UNIQUE(TOCOL(x)))),1)
13		3	b								a	2	3	1	xx
14											c	3	7	2	xy
15		functions that accept multiple arguments but return single results									d	4	a	3	yx
16												7	b		yy
17	1	1					=SUM(B17:C18,B20:E20,B22:C23,B25#)
18		3	7				167				arrays that have no 0's
19											1			2			3
20	2	1	4	a	-3						4	3		a	0		7	d	-4
21												5		4	n		g	8	0.01
22	3	12	12											k	0
23		9	76		=ARRAYS(B17:C18,B20:E20,B22:C23,B25#)(LAMBDA(x,SUM(x)))
24							11				=AND(K20:L21,N20:O22,Q20:S21)
25	4	1	2	3			2				FALSE
26		4	5	6			109
27		7	8	9			45				=ARRAYS(K20:L21,N20:O22,Q20:S21)(LAMBDA(x,AND(x)))
28											TRUE
29											FALSE
30											TRUE
31
Sheet11

Cell Formulas
Range	Formula
E5,K27,K24,G17,K12	E5	=FORMULATEXT(E6)
E6:E10	E6	=ARRAYS(B4,B6,B8,B10,B12:C13)(LAMBDA(x,TYPE(x)))
B10	B10	=NA()
K13:O16	K13	=ARRAYS(K3:M4,K6:M8,O3:T3,O6:P7,R5:S8)(LAMBDA(x,SORT(UNIQUE(TOCOL(x)))),1)
G18	G18	=SUM(B17:C18,B20:E20,B22:C23,B25#)
E23	E23	=FORMULATEXT(G24)
G24:G27	G24	=ARRAYS(B17:C18,B20:E20,B22:C23,B25#)(LAMBDA(x,SUM(x)))
B25:D27	B25	=SEQUENCE(3,3)
K25	K25	=AND(K20:L21,N20:O22,Q20:S21)
K28:K30	K28	=ARRAYS(K20:L21,N20:O22,Q20:S21)(LAMBDA(x,AND(x)))
Dynamic array formulas.

 

[Xlambda]

Book3.xlsx
	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
1
2		Tb	v1	v2	v3			=ARRAYS(B3:E7,B8:E12,B13:E17,B18:E22)(LAMBDA(x,LET(y,BYCOL(x,LAMBDA(x,SUM(x))),IF(y,y,"Sum"))))
3		A	-49	78	37			Sum	3	81	-29
4		A	41	34	21			Sum	20	-82	232			=ARRAYS(B3:E7,B8:E12,B13:E17,B18:E22)(LAMBDA(x,LET(y,BYCOL(x,LAMBDA(x,AVERAGE(x))),IFERROR(y,"Avg"))))
5		A	10	-70	-17			Sum	24	67	20			Avg	0.6	16.2	-5.8
6		A	-22	63	-48			Sum	163	41	-256			Avg	4	-16.4	46.4
7		A	23	-24	-22									Avg	4.8	13.4	4
8		B	43	45	50									Avg	32.6	8.2	-51.2
9		B	-16	-92	71
10		B	60	33	27
11		B	-38	-40	10			=NARRINS(,B2:E2,B3:E22,H3#)						=NARRINS(,B2:E2,B3:E22,N5#)
12		B	-29	-28	74			Tb	v1	v2	v3			Tb	v1	v2	v3
13		C	-14	92	-4			A	-49	78	37			A	-49	78	37
14		C	94	-55	82			A	41	34	21			A	41	34	21
15		C	95	-17	-65			A	10	-70	-17			A	10	-70	-17
16		C	-60	-19	6			A	-22	63	-48			A	-22	63	-48
17		C	-91	66	1			A	23	-24	-22			A	23	-24	-22
18		D	78	-17	-36			Sum	3	81	-29			Avg	0.6	16.2	-5.8
19		D	12	74	-80			B	43	45	50			B	43	45	50
20		D	-9	-34	-86			B	-16	-92	71			B	-16	-92	71
21		D	71	88	-17			B	60	33	27			B	60	33	27
22		D	11	-70	-37			B	-38	-40	10			B	-38	-40	10
23								B	-29	-28	74			B	-29	-28	74
24								Sum	20	-82	232			Avg	4	-16.4	46.4
25								C	-14	92	-4			C	-14	92	-4
26								C	94	-55	82			C	94	-55	82
27								C	95	-17	-65			C	95	-17	-65
28								C	-60	-19	6			C	-60	-19	6
29								C	-91	66	1			C	-91	66	1
30								Sum	24	67	20			Avg	4.8	13.4	4
31								D	78	-17	-36			D	78	-17	-36
32								D	12	74	-80			D	12	74	-80
33								D	-9	-34	-86			D	-9	-34	-86
34								D	71	88	-17			D	71	88	-17
35								D	11	-70	-37			D	11	-70	-37
36								Sum	163	41	-256			Avg	32.6	8.2	-51.2
37
Sheet12

Cell Formulas
Range	Formula
H2,N11,H11,N4	H2	=FORMULATEXT(H3)
C3:E22	C3	=RANDARRAY(20,3,-99,99,1)
H3:K6	H3	=ARRAYS(B3:E7,B8:E12,B13:E17,B18:E22)(LAMBDA(x,LET(y,BYCOL(x,LAMBDA(x,SUM(x))),IF(y,y,"Sum"))))
N5:Q8	N5	=ARRAYS(B3:E7,B8:E12,B13:E17,B18:E22)(LAMBDA(x,LET(y,BYCOL(x,LAMBDA(x,AVERAGE(x))),IFERROR(y,"Avg"))))
H12:K36	H12	=NARRINS(,B2:E2,B3:E22,H3#)
N12:Q36	N12	=NARRINS(,B2:E2,B3:E22,N5#)
Dynamic array formulas.

 

[Xlambda]

Book3.xlsx
	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
1		report comes from =>			MP thread post #10
2
3		East					North					South					West
4		Crested	Majestic	Quad	Sunset	Yanaki	Crested	Majestic	Quad	Sunset	Yanaki	Crested	Majestic	Quad	Sunset	Yanaki	Crested	Majestic	Quad	Sunset	Yanaki
5				94								172.9			61.75				97
6		131.6		94	122.2	113.74			96							94.05	81.48
7		78.96			146.64								114
8			248.16			103.4								114				69.84		75.66
9			124.08		171.08								79.8	123.5				69.84		163.93
10					122.2	103.4	295.68						136.8							126.1	266.75
11									105.6		52.8	133	136.8				81.48	128.04	252.2
12				112.8					124.8				114	57	222.3		135.8
13
14		=ARRAYS(B3:F12,G3:K12,L3:P12,Q3:U12)(LAMBDA(x,LET(b,BYROW(x,LAMBDA(y,SUM(y))),i,IF(SEQUENCE(ROWS(b))=1,"Tot",b),IF(i=0,"",i))),1)
15		↓↓↓
16		↓↓↓					=NARRINS(1,,B3:U12,B17#)
17		Tot	Tot	Tot	Tot		East					Tot	North					Tot	South					Tot	West					Tot
18							Crested	Majestic	Quad	Sunset	Yanaki		Crested	Majestic	Quad	Sunset	Yanaki		Crested	Majestic	Quad	Sunset	Yanaki		Crested	Majestic	Quad	Sunset	Yanaki
19		94		234.65	97				94			94							172.9			61.75		234.65			97			97
20		461.54	96	94.05	81.48		131.6		94	122.2	113.74	461.54			96			96					94.05	94.05	81.48					81.48
21		225.6		114			78.96			146.64		225.6								114				114
22		351.56		114	145.5			248.16			103.4	351.56									114			114		69.84		75.66		145.5
23		295.16		203.3	233.77			124.08		171.08		295.16								79.8	123.5			203.3		69.84		163.93		233.77
24		225.6	295.68	136.8	392.85					122.2	103.4	225.6	295.68					295.68		136.8				136.8				126.1	266.75	392.85
25			158.4	269.8	461.72										105.6		52.8	158.4	133	136.8				269.8	81.48	128.04	252.2			461.72
26		112.8	124.8	393.3	135.8				112.8			112.8			124.8			124.8		114	57	222.3		393.3	135.8					135.8
27
28
Sheet13

Cell Formulas
Range	Formula
B14	B14	=FORMULATEXT(B17)
G16	G16	=FORMULATEXT(G17)
B17:E26	B17	=ARRAYS(B3:F12,G3:K12,L3:P12,Q3:U12)(LAMBDA(x,LET(b,BYROW(x,LAMBDA(y,SUM(y))),i,IF(SEQUENCE(ROWS(b))=1,"Tot",b),IF(i=0,"",i))),1)
G17:AD26	G17	=NARRINS(1,,B3:U12,B17#)
Dynamic array formulas.

 

[st001]

Lovely function!
I was trying to use it to solve this

using Lambdas only, but failed miserably : (

 

[Xlambda]

Lovely function!
I was trying to use it to solve this

using Lambdas only, but failed miserably : (

Hi st001, thanks!! . Cool challenge. This is the first draft I came up with. Some lambda query.
The main purpose was to prove that NARRINS could be useful
Could have been much shorter if I would have used my old formulas ASTACK and AUNSTACK that were designed for these scenarios.
Maybe I will write a variant using them also.
CITIES(tb)

=LAMBDA(tb,
    LET(
        r, ROWS(tb),
        c, TAKE(tb, 1),
        cy, FILTER(c, c <> ""),
        n, COLUMNS(cy),
        y, REPT(cy, SEQUENCE(r) ^ 0),
        a, NARRINS(1, , tb, y),
        w, WRAPROWS(TOCOL(DROP(a, 2)), 3),
        s, SORTBY(w, MOD(SEQUENCE((r - 2) * n) - 1, n)),
        b, CHOOSECOLS(FILTER(s, TAKE(s, , 1) <> ""), {3, 1, 2}),
        VSTACK({"City", "Name", "Age"}, b)
    )
)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2			Mumbai		New York		Hong Kong		London		Mumbai		New York		Hong Kong		London
3		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
5		B	12	F	12	J	12	O	5		B	12	F	12	J	12	O	5
6		c	18	G	18	K	18	p	5		c	18	G	18	K	18	p	5
7		D	10			L	19	Q	5		D	10			L	19	Q	5
8						M	20								M	20
9
10			=CITIES(B2:I8)								It was not clear where the cities are placed, above Name, or above Age.
11			City	Name	Age						Anyhow, lambda works both ways
12			Mumbai	A	14
13			Mumbai	B	12						=CITIES(K2:R8)
14			Mumbai	c	18						City	Name	Age
15			Mumbai	D	10						Mumbai	A	14
16			New York	E	14						Mumbai	B	12
17			New York	F	12						Mumbai	c	18
18			New York	G	18						Mumbai	D	10
19			Hong Kong	I	14						New York	E	14
20			Hong Kong	J	12						New York	F	12
21			Hong Kong	K	18						New York	G	18
22			Hong Kong	L	19						Hong Kong	I	14
23			Hong Kong	M	20						Hong Kong	J	12
24			London	N	5						Hong Kong	K	18
25			London	O	5						Hong Kong	L	19
26			London	p	5						Hong Kong	M	20
27			London	Q	5						London	N	5
28											London	O	5
29											London	p	5
30											London	Q	5
31
Sheet14

Cell Formulas
Range	Formula
C10,K13	C10	=FORMULATEXT(C11)
C11:E27	C11	=CITIES(B2:I8)
K14:M30	K14	=CITIES(K2:R8)
Dynamic array formulas.

 

[Xlambda]

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		If the table gets extended, more cities and more names
3													=CITIES(B4:K12)
4		Mumbai		New York		Hong Kong		London		Madrid			City	Name	Age
5		Name	Age	Name	Age	Name	Age	Name	Age	Name	Age		Mumbai	A	14
6		A	14	E	14	I	14	N	5	R	23		Mumbai	B	12
7		B	12	F	12	J	12	O	5	S	32		Mumbai	c	18
8		c	18	G	18	K	18	p	5	T	43		Mumbai	D	10
9		D	10			L	19	Q	5	U	18		New York	E	14
10						M	20			V	12		New York	F	12
11						MA	17			X	23		New York	G	18
12						MB	20						Hong Kong	I	14
13													Hong Kong	J	12
14													Hong Kong	K	18
15													Hong Kong	L	19
16													Hong Kong	M	20
17													Hong Kong	MA	17
18													Hong Kong	MB	20
19													London	N	5
20													London	O	5
21													London	p	5
22													London	Q	5
23													Madrid	R	23
24													Madrid	S	32
25													Madrid	T	43
26													Madrid	U	18
27													Madrid	V	12
28													Madrid	X	23
29
Sheet15

Cell Formulas
Range	Formula
M3	M3	=FORMULATEXT(M4)
M4:O28	M4	=CITIES(B4:K12)
Dynamic array formulas.

 

[Xlambda]

Same task solved using ASTACK that calls AUNSTACK, step by step

Book3.xlsx
	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
1		Concept CTYSTK using ASTACK,NARRINS
2																		4. stacking				5. clms order+exclude blanks
3		Mumbai		New York		Hong Kong		London		Madrid								=ASTACK(B28:P34,3)				=LET(a,R4#,CHOOSECOLS(FILTER(a, TAKE(a, , 1) <> ""), {3,1,2}))
4		Name	Age	Name	Age	Name	Age	Name	Age	Name	Age							A	14	Mumbai		Mumbai	A	14
5		A	14	E	14	I	14	N	5	R	23							B	12	Mumbai		Mumbai	B	12
6		B	12	F	12	J	12	O	5	S	32							c	18	Mumbai		Mumbai	c	18
7		c	18	G	18	K	18	p	5	T	43							D	10	Mumbai		Mumbai	D	10
8		D	10			L	19	Q	5	U	18									Mumbai		New York	E	14
9						M	20			V	12									Mumbai		New York	F	12
10						MA	17			X	23									Mumbai		New York	G	18
11						MB	20											E	14	New York		Hong Kong	I	14
12																		F	12	New York		Hong Kong	J	12
13		1. extract cities						2. prepare cities to be inserted										G	18	New York		Hong Kong	K	18
14		=LET(c,B3:K3,FILTER(c,c<>""))						=REPT(B15#,SEQUENCE(ROWS(B3:K11))^0)												New York		Hong Kong	L	19
15		Mumbai	New York	Hong Kong	London	Madrid		Mumbai	New York	Hong Kong	London	Madrid								New York		Hong Kong	M	20
16								Mumbai	New York	Hong Kong	London	Madrid								New York		Hong Kong	MA	17
17								Mumbai	New York	Hong Kong	London	Madrid								New York		Hong Kong	MB	20
18								Mumbai	New York	Hong Kong	London	Madrid						I	14	Hong Kong		London	N	5
19								Mumbai	New York	Hong Kong	London	Madrid						J	12	Hong Kong		London	O	5
20								Mumbai	New York	Hong Kong	London	Madrid						K	18	Hong Kong		London	p	5
21								Mumbai	New York	Hong Kong	London	Madrid						L	19	Hong Kong		London	Q	5
22								Mumbai	New York	Hong Kong	London	Madrid						M	20	Hong Kong		Madrid	R	23
23								Mumbai	New York	Hong Kong	London	Madrid						MA	17	Hong Kong		Madrid	S	32
24		3. inserting																MB	20	Hong Kong		Madrid	T	43
25		=NARRINS(1,,B3:K11,H15#)																N	5	London		Madrid	U	18
26		Mumbai		Mumbai	New York		New York	Hong Kong		Hong Kong	London		London	Madrid		Madrid		O	5	London		Madrid	V	12
27		Name	Age	Mumbai	Name	Age	New York	Name	Age	Hong Kong	Name	Age	London	Name	Age	Madrid		p	5	London		Madrid	X	23
28		A	14	Mumbai	E	14	New York	I	14	Hong Kong	N	5	London	R	23	Madrid		Q	5	London
29		B	12	Mumbai	F	12	New York	J	12	Hong Kong	O	5	London	S	32	Madrid				London
30		c	18	Mumbai	G	18	New York	K	18	Hong Kong	p	5	London	T	43	Madrid				London
31		D	10	Mumbai			New York	L	19	Hong Kong	Q	5	London	U	18	Madrid				London
32				Mumbai			New York	M	20	Hong Kong			London	V	12	Madrid		R	23	Madrid
33				Mumbai			New York	MA	17	Hong Kong			London	X	23	Madrid		S	32	Madrid
34				Mumbai			New York	MB	20	Hong Kong			London			Madrid		T	43	Madrid
35																		U	18	Madrid
36																		V	12	Madrid
37																		X	23	Madrid
38																				Madrid
39
Sheet16

Cell Formulas
Range	Formula
R3,B25,H14,B14,V3	R3	=FORMULATEXT(R4)
R4:T38	R4	=ASTACK(B28:P34,3)
V4:X27	V4	=LET(a,R4#,CHOOSECOLS(FILTER(a, TAKE(a, , 1) <> ""), {3,1,2}))
B15:F15	B15	=LET(c,B3:K3,FILTER(c,c<>""))
H15:L23	H15	=REPT(B15#,SEQUENCE(ROWS(B3:K11))^0)
B26:P34	B26	=NARRINS(1,,B3:K11,H15#)
Dynamic array formulas.

 

[Xlambda]

CTYSTK(tb)

=LAMBDA(tb,
    LET(
        c, TAKE(tb, 1),
        cy, FILTER(c, c <> ""),
        y, REPT(cy, SEQUENCE(ROWS(tb)) ^ 0),
        a, ASTACK(DROP(NARRINS(1, , tb, y), 2), 3),
        b, CHOOSECOLS(FILTER(a, TAKE(a, , 1) <> ""), {3, 1, 2}),
        VSTACK({"City", "Name", "Age"}, b)
    )
)

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		=CTYSTK(B2:I8)				=CTYSTK(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
Sheet18

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

 

[Xlambda]

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Concept STKCTY, using only ASTACK
2											1. extracting cities
3		Mumbai		New York		Hong Kong		London			=FILTER(B3:I3,B3:I3<>"")
4		Name	Age	Name	Age	Name	Age	Name	Age		Mumbai	New York	Hong Kong	London
5		A	14	E	14	I	14	N	5
6		B	12	F	12	J	12	O	5
7		c	18	G	18	K	18	p	5
8		D	10			L	19	Q	5
9						M	20
10
11		2. stacking			3. 1clm for FILTER		4. index for cities		5. appending cities				6. filtering
12		=ASTACK(B5:I9,2)			=TAKE(B13#,,1)				=HSTACK(INDEX(K4#,G13#),B13:C31)				=FILTER(I13#,E13#<>"")
13		A	14		A		1		Mumbai	A	14		Mumbai	A	14
14		B	12		B		1		Mumbai	B	12		Mumbai	B	12
15		c	18		c		1		Mumbai	c	18		Mumbai	c	18
16		D	10		D		1		Mumbai	D	10		Mumbai	D	10
17							1		Mumbai				New York	E	14
18		E	14		E		2		New York	E	14		New York	F	12
19		F	12		F		2		New York	F	12		New York	G	18
20		G	18		G		2		New York	G	18		Hong Kong	I	14
21							2		New York				Hong Kong	J	12
22							2		New York				Hong Kong	K	18
23		I	14		I		3		Hong Kong	I	14		Hong Kong	L	19
24		J	12		J		3		Hong Kong	J	12		Hong Kong	M	20
25		K	18		K		3		Hong Kong	K	18		London	N	5
26		L	19		L		3		Hong Kong	L	19		London	O	5
27		M	20		M		3		Hong Kong	M	20		London	p	5
28		N	5		N		4		London	N	5		London	Q	5
29		O	5		O		4		London	O	5
30		p	5		p		4		London	p	5
31		Q	5		Q		4		London	Q	5
32							4		London	#N/A	#N/A
33							=QUOTIENT(SEQUENCE(ROWS(B13#))-1,ROWS(B3:I9)-2)+1
34
Sheet17

Cell Formulas
Range	Formula
K3,I12,M12,E12,B12	K3	=FORMULATEXT(K4)
K4:N4	K4	=FILTER(B3:I3,B3:I3<>"")
B13:C32	B13	=ASTACK(B5:I9,2)
E13:E32	E13	=TAKE(B13#,,1)
G13:G32	G13	=QUOTIENT(SEQUENCE(ROWS(B13#))-1,ROWS(B3:I9)-2)+1
I13:K32	I13	=HSTACK(INDEX(K4#,G13#),B13:C31)
M13:O28	M13	=FILTER(I13#,E13#<>"")
G33	G33	=FORMULATEXT(G13)
Dynamic array formulas.