Join dynamic array formulae

[Georgiboy]

Hi all,

I have been learning dynamic array formulae as i move from a more VBA background to formulae. I was trying to help someone on here earlier and got very stuck, we are talking about three hours of stuck trying to figure this out and i feel it should be quite simple..

I have the below and i have been trying to join the formula in column E to the formula in column C:

Book1
	A	B	C	D	E
1	Example		Code	Existing Numbers	Missing Numbers
2	Example1		Example	0, 1, 2, 3, 5, 6, 8, 9	4, 7
3	Example2		BSmith	0, 1, 3, 6, 7, 9, 10, 11, 12, 13, 15, 17, 18, 19	2, 4, 5, 8, 14, 16
4	Example3		Dave	1, 2, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 16, 17, 18, 19	3, 9, 15
5	Example5
6	Example6
7	Example8
8	Example9
9	BSmith
10	BSmith1
11	BSmith3
12	BSmith6
13	BSmith7
14	BSmith9
15	BSmith10
16	BSmith11
17	BSmith12
18	BSmith13
19	BSmith15
20	BSmith17
21	BSmith18
22	BSmith19
23	Dave1
24	Dave2
25	Dave4
26	Dave5
27	Dave6
28	Dave7
29	Dave8
30	Dave10
31	Dave11
32	Dave12
33	Dave13
34	Dave14
35	Dave16
36	Dave17
37	Dave18
38	Dave19
Sheet1

Cell Formulas
Range	Formula
C2:D4	C2	=LET( nums,IFERROR(--BYROW(A1:A38,LAMBDA(x,RIGHT(x,LEN(x)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},x&"0123456789"))+1))),0), words,SUBSTITUTE(A1:A38,nums,""), u,UNIQUE(words), result,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(nums,words=br)))), HSTACK(u,result))
E2:E4	E2	=BYROW(C2#,LAMBDA(x,TEXTJOIN(", ",TRUE,LET(ts,--TEXTSPLIT(INDEX(x,,2),", "),sq,SEQUENCE(,MAX(ts)),FILTER(sq,NOT(ISNUMBER(MATCH(sq,ts,0))))))))
Dynamic array formulas.

As above it works but i wanted them in one formula so i could omit the 'Existing Numbers' column and leave 'Code' & 'Missing Numbers' columns.

Below is my best go at joining them together:

Book1
	A	B	C	D	E
1	Example		Code	Existing Numbers	Missing Numbers
2	Example1		Example	0, 1, 2, 3, 5, 6, 8, 9	#CALC!
3	Example2		BSmith	0, 1, 3, 6, 7, 9, 10, 11, 12, 13, 15, 17, 18, 19	#CALC!
4	Example3		Dave	1, 2, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 16, 17, 18, 19	#CALC!
5	Example5
6	Example6
7	Example8
8	Example9
9	BSmith
10	BSmith1
11	BSmith3
12	BSmith6
13	BSmith7
14	BSmith9
15	BSmith10
16	BSmith11
17	BSmith12
18	BSmith13
19	BSmith15
20	BSmith17
21	BSmith18
22	BSmith19
23	Dave1
24	Dave2
25	Dave4
26	Dave5
27	Dave6
28	Dave7
29	Dave8
30	Dave10
31	Dave11
32	Dave12
33	Dave13
34	Dave14
35	Dave16
36	Dave17
37	Dave18
38	Dave19
Sheet2

Cell Formulas
Range	Formula
C2:E4	C2	=LET( nums,IFERROR(--BYROW(A1:A38,LAMBDA(x,RIGHT(x,LEN(x)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},x&"0123456789"))+1))),0), words,SUBSTITUTE(A1:A38,nums,""), u,UNIQUE(words), result,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(nums,words=br)))), tj,BYROW(result,LAMBDA(x,TEXTJOIN(", ",TRUE,LET(ts,--TEXTSPLIT(x,", "),sq,SEQUENCE(,MAX(ts)),FILTER(sq,NOT(ISNUMBER(MATCH(sq,ts,0)))))))), HSTACK(u,result,tj))
Dynamic array formulas.

I suppose i am looking for an explanation of what i have done wrong and a way of doing it right?

Not really looking for a faster/ better way to do it - just trying to learn but have got rather stuck

 

[awoohaw]

Do you want the two arrays to process with each other?
What kind of join? (INNER only, LEFT OUTER + Whats in RIGHT not IN LEFT).

Does the column C formula result in a 2 column array? If so, I think you need to "pad" a placeholder in in the array based on column E.

 

[Peter_SSs]

Is this what you mean?

Georgiboy.xlsm
	A	B	C
1	Example	Example	4, 7
2	Example1	BSmith	2, 4, 5, 8, 14, 16
3	Example2	Dave	3, 9, 15
4	Example3
5	Example5
6	Example6
7	Example8
8	Example9
9	BSmith
10	BSmith1
11	BSmith3
12	BSmith6
13	BSmith7
14	BSmith9
15	BSmith10
16	BSmith11
17	BSmith12
18	BSmith13
19	BSmith15
20	BSmith17
21	BSmith18
22	BSmith19
23	Dave1
24	Dave2
25	Dave4
26	Dave5
27	Dave6
28	Dave7
29	Dave8
30	Dave10
31	Dave11
32	Dave12
33	Dave13
34	Dave14
35	Dave16
36	Dave17
37	Dave18
38	Dave19
Sheet1

Cell Formulas
Range	Formula
B1:C3	B1	=LET(rng,A1:A38,tb,TEXTBEFORE(rng&1,{0,1,2,3,4,5,6,7,8,9}),u,UNIQUE(tb),r,REPLACE(rng,1,LEN(tb),""),n,IF(r="",0,r+0), HSTACK(u,BYROW(u,LAMBDA(x,LET(f,FILTER(n,LEFT(rng,LEN(x))=x),s,SEQUENCE(MAX(f)),TEXTJOIN(", ",1,IF(ISNUMBER(MATCH(s,f,0)),"",s)))))))
Dynamic array formulas.

 

[Georgiboy]

Thank you @Peter_SSs that does do what i needed it to, however my simple brain is still somewhat perplexed on this.

If you could help me understand the below (smaller piece of info to work with) i would be happy.

On the below i have created a formula that is close to what i want to get to which is basically a filter of names by unique type:

Book1
	A	B	C	D	E
1	Type	Name		Fruit	Apple, Grapes, Plum, Orange, Pear
2	Fruit	Apple		Vegetable	Carrot, Beans, Cucumber, Cabbage
3	Fruit	Grapes		Frozen	Pies, Sausages, Cakes
4	Fruit	Plum
5	Fruit	Orange
6	Fruit	Pear
7	Vegetable	Carrot
8	Vegetable	Beans
9	Vegetable	Cucumber
10	Vegetable	Cabbage
11	Frozen	Pies
12	Frozen	Sausages
13	Frozen	Cakes
Sheet1

Cell Formulas
Range	Formula
D1:E3	D1	=LET(u,UNIQUE(Table2[Type]),f,BYROW(u,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER(Table2[Name],Table2[Type]=x)))),HSTACK(u,f))
Dynamic array formulas.

I am trying to get to the below result with one similar formula to the above:

Book1
	A	B	C	D	E	F	G	H	I
1	Type	Name		Fruit	Apple	Grapes	Plum	Orange	Pear
2	Fruit	Apple		Vegetable	Carrot	Beans	Cucumber	Cabbage
3	Fruit	Grapes		Frozen	Pies	Sausages	Cakes
4	Fruit	Plum
5	Fruit	Orange
6	Fruit	Pear
7	Vegetable	Carrot
8	Vegetable	Beans
9	Vegetable	Cucumber
10	Vegetable	Cabbage
11	Frozen	Pies
12	Frozen	Sausages
13	Frozen	Cakes
Sheet2

Cell Formulas
Range	Formula
D1:D3	D1	=UNIQUE(Table24[Type])
E1:I1,E3:G3,E2:H2	E1	=TOROW(FILTER(Table24[Name],Table24[Type]=D1))
Dynamic array formulas.

I think i am missing something in the logic to allow the columns to spill. I have not started aa new thread as i believe i am missing the same piece of logic i was in the first formula. I just can'e get my head around why whatever i try does not work, i just get left with a #CALC error.

Why is is so difficult to filter to a unique list in the same formula?

Below is a formula i have tried:

=LET(u,TRANSPOSE(UNIQUE(Table245[Type])),f,BYCOL(u,LAMBDA(x,FILTER(Table245[Name],Table245[Type]=x))),HSTACK(u,f))

The end goal here was to get to the unique types to be the headers and to have the filtered names to be under the headers.

 

[Peter_SSs]

Along these lines?

Georgiboy.xlsm
	A	B	C	D	E	F	G	H	I
1	Type	Name		Fruit	Apple	Grapes	Plum	Orange	Pear
2	Fruit	Apple		Vegetable	Carrot	Beans	Cucumber	Cabbage
3	Fruit	Grapes		Frozen	Pies	Sausages	Cakes
4	Fruit	Plum
5	Fruit	Orange
6	Fruit	Pear
7	Vegetable	Carrot
8	Vegetable	Beans
9	Vegetable	Cucumber
10	Vegetable	Cabbage
11	Frozen	Pies
12	Frozen	Sausages
13	Frozen	Cakes
Sheet3

Cell Formulas
Range	Formula
D1:I3	D1	=LET(u,UNIQUE(Table2[Type]),m,MAX(COUNTIF(Table2[Type],u)),f,WRAPROWS(TEXTSPLIT(TEXTJOIN(",",0,BYROW(u,LAMBDA(x,TEXTJOIN(",",0,EXPAND(FILTER(Table2[Name],Table2[Type]=x),m,,""))))),","),m),HSTACK(u,f))
Dynamic array formulas.

 

[Georgiboy]

Thanks @Peter_SSs will spend some time looking and try to get my head around it.

On having a quick look: the first inner TEXTJOIN creates what I had already, the outer TEXTJOIN joins all of the inner joins together then the outer TEXTSPLIT creates one long array of values (in one row) that the WRAPROWS then splits based on the count of names found in each type 'm'?

Cheers

 

[Peter_SSs]

You're welcome.

If your data is large it may fail as TEXTJOIN has a limit of 32,767 characters.