textjoin + filter formula

[valmir]

Hello everyone!
For better clarification, I have attached a sample file:
1. The formula in "D1" instead of "A2", I want to search by the name which is "ACA"
2. I also would like to combine two different names instead of just one, for example COU+DCH
3. In E1 I would like to have the number of occurrences. For ACA the result should be 3
I hope my explanation is clear enough!
Thanks!

zztest file.xlsx
	A	B	C	D	E
1	14A	1979	ACA	1979, 1981, 1982
2	ACA	1979	COU	1979, 1981, 1982, 1984
3	COU	1979	DCH	1979, 1981, 1982, 1984
4	DCH	1979	DTA	1979, 1981, 1982, 1984
5	DIA	1979	DVE	1979
6	DTA	1979
7	DVE	1979
8	EST	1979
9	FCC	1979
10	FCU	1980
11	FHL	1980
12	MAM	1980
13	NAC	1980
14	PAL	1980
15	PRI	1980
16	SAG	1980
17	SAS	1980
18	WEL	1980
19	ACA	1981
20	COU	1981
21	DCH	1981
22	DTA	1981
23	FCU	1981
24	ACA	1982
25	COU	1982
26	DBG	1982
27	DCH	1982
28	DTA	1982
29	PET	1983
30	PHU	1983
31	PRI	1983
32	PRM	1983
33	PRO	1983
34	COU	1984
35	DBG	1984
36	DCH	1984
37	DTA	1984
38	FHL	1984
39	INT	1984
40	MAM	1984
41	NAC	1984
42	PET	1984
43	PHU	1984
44	PRI	1984
45	PRM	1984
46	PRO	1984
47	SAG	1984
Sheet1

Cell Formulas
Range	Formula
D1:D3	D1	=TEXTJOIN(", ", TRUE, FILTER($B:$B,A:A=A2))
D4:D5	D4	=TEXTJOIN(", ", TRUE, FILTER($B:$B,A:A=A6))

 

[shift-del]

That would be this.

Mappe17
	D	E	F	G
1	ACA	1979	1979; 1981; 1982	3
2		1981
3		1982
4
5
6	COU	1979	1979; 1979; 1981; 1981; 1982; 1982; 1984; 1984	8
7	DCH	1979
8		1981
9		1981
10		1982
11		1982
12		1984
13		1984
Tabelle1

Cell Formulas
Range	Formula
E1:E3	E1	=FILTER(B:B,A:A=D1)
F1,F6	F1	=TEXTJOIN("; ",TRUE,E1#)
G1,G6	G1	=COUNT(E1#)
E6:E13	E6	=FILTER(B1:B47,COUNTIFS(D6:D7,A1:A47))
Dynamic array formulas.

 

[valmir]

That would be this.

Mappe17
	D	E	F	G
1	ACA	1979	1979; 1981; 1982	3
2		1981
3		1982
4
5
6	COU	1979	1979; 1979; 1981; 1981; 1982; 1982; 1984; 1984	8
7	DCH	1979
8		1981
9		1981
10		1982
11		1982
12		1984
13		1984
Tabelle1

Cell Formulas
Range	Formula
E1:E3	E1	=FILTER(B:B,A:A=D1)
F1,F6	F1	=TEXTJOIN("; ",TRUE,E1#)
G1,G6	G1	=COUNT(E1#)
E6:E13	E6	=FILTER(B1:B47,COUNTIFS(D6:D7,A1:A47))
Dynamic array formulas.

So, is it not possible to use the name "ACA" in the formula? That's what I am looking for...

 

[shift-del]

 

[valmir]

I figured out myself. The formula in D1 should look like this:

=TEXTJOIN(", ", TRUE, FILTER($B:$B,A:A="ACA"))

I tried this before, I just forgot to include the quotation marks and was getting an error as a result. Now I just need someone to help with point 2 which is searching for two names instead of one!

 

[valmir]

The solution to point 2 in my request looks like this:

=(TEXTJOIN(", "; TRUE; IF(B:B="COU"; C:C; "")) & IF(COUNTIF(B:B; "COU")>0; "; "; "") & TEXTJOIN(", "; TRUE; IF(B:B="DCH"; C:C; "")))