textjoin the result of filter function

[ExcelNewbie2020]

i have a current filter function that is working perfectly =FILTER(FILTER(A2:C12,ISNA(MATCH($A$2:$A$12,$L$2:$L$4,0))*(C2:C12>0)),{1,0,1}). How to modify this formula to use textjoin for the result.

hlink.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	name	dept	qty			with filter function						excluded name
2	name1	AAA	20			name	qty					name4
3	name2	BBB	30			name1	20					name5
4	name3	CCC	20			name2	30					name6
5	name4	AAA	40			name3	20
6	name5	BBB	50			name8	100
7	name6	CCC	10			name11	10
8	name7	AAA
9	name8	BBB	100
10	name9	CCC				Expected result
11	name10	CCC				name1 - 20
12	name11	AAA	10			name2 - 30
13						name3 - 20
14						name8 - 100
15						name11 - 10
16
17
18
19
20
21
22
Sheet4

Cell Formulas
Range	Formula
F3:G7	F3	=FILTER(FILTER(A2:C12,ISNA(MATCH($A$2:$A$12,$L$2:$L$4,0))*(C2:C12>0)),{1,0,1})
Dynamic array formulas.

 

[Peter_SSs]

What about this?

23 04 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	name	dept	qty			with filter function						excluded name
2	name1	AAA	20			name	qty					name4
3	name2	BBB	30			name1	20					name5
4	name3	CCC	20			name2	30					name6
5	name4	AAA	40			name3	20
6	name5	BBB	50			name8	100
7	name6	CCC	10			name11	10
8	name7	AAA
9	name8	BBB	100
10	name9	CCC				Expected result
11	name10	CCC				name1 - 20
12	name11	AAA	10			name2 - 30
13						name3 - 20
14						name8 - 100
15						name11 - 10
16
17						Result
18						name1 - 20
19						name2 - 30
20						name3 - 20
21						name8 - 100
22						name11 - 10
23
TEXTJOIN

Cell Formulas
Range	Formula
F3:G7	F3	=FILTER(FILTER(A2:C12,ISNA(MATCH($A$2:$A$12,$L$2:$L$4,0))*(C2:C12>0)),{1,0,1})
F18:F22	F18	=FILTER(A2:A12&" - "&C2:C12,ISNA(MATCH(A2:A12,L2:L4,0))*(C2:C12>0))
Dynamic array formulas.

 

[JvdV]

Here is one option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	name	dept	qty		name1 - 20							excluded name
2	name1	AAA	20		name2 - 30							name4
3	name2	BBB	30		name3 - 20							name5
4	name3	CCC	20		name8 - 100							name6
5	name4	AAA	40		name11 - 10
6	name5	BBB	50
7	name6	CCC	10
8	name7	AAA
9	name8	BBB	100
10	name9	CCC
11	name10	CCC
12	name11	AAA	10
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=FILTER(A2:A12&" - "&C2:C12,NOT(COUNTIF(L2:L4,A2:A12))*C2:C12)
Dynamic array formulas.

 

[ExcelNewbie2020]

What about this?

23 04 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	name	dept	qty			with filter function						excluded name
2	name1	AAA	20			name	qty					name4
3	name2	BBB	30			name1	20					name5
4	name3	CCC	20			name2	30					name6
5	name4	AAA	40			name3	20
6	name5	BBB	50			name8	100
7	name6	CCC	10			name11	10
8	name7	AAA
9	name8	BBB	100
10	name9	CCC				Expected result
11	name10	CCC				name1 - 20
12	name11	AAA	10			name2 - 30
13						name3 - 20
14						name8 - 100
15						name11 - 10
16
17						Result
18						name1 - 20
19						name2 - 30
20						name3 - 20
21						name8 - 100
22						name11 - 10
23
TEXTJOIN

Cell Formulas
Range	Formula
F3:G7	F3	=FILTER(FILTER(A2:C12,ISNA(MATCH($A$2:$A$12,$L$2:$L$4,0))*(C2:C12>0)),{1,0,1})
F18:F22	F18	=FILTER(A2:A12&" - "&C2:C12,ISNA(MATCH(A2:A12,L2:L4,0))*(C2:C12>0))
Dynamic array formulas.

yes. this worked. Thank you sir

 

[Peter_SSs]

You're welcome. Glad it worked for you.