How to turn the FILTER output array into a single row and remove all blanks and error values?

[Rnkhch]

Hello,

I have the following array and I filter for those values that correspond to 2 in columns A. How can I have the output in a single row that will be as long as there are matching values and with all blanks and errors removed? Currently I get a 0 (cell I2) for the D4 blank cell.

Thanks for any input!

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	8	18	19	5		7	2	0	18
3	1	5	2	4	3		#N/A	10	3	17
4	2	7	2		18		14	7	15	15
5	3	8	15	6	2		14	14	6	18
6	2	#N/A	10	3	17		7	14	6	6
7	2	14	7	15	15
8	3	12	11	9	8
9	1	15	17	2	4
10	2	14	14	6	18
11	1	2	2	16	10
12	2	7	14	6	6
13
Sheet1

Cell Formulas
Range	Formula
G2:J6	G2	=FILTER(B2:E12,A2:A12=2)
Dynamic array formulas.

 

[Peter_SSs]

Do you yet have the TOROW() function in your Excel version?

22 06 27.xlsm
	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	1	8	18	19	5		7	2	18	10	3	17	14	7	15	15	14	14	6	18	7	14	6	6
3	1	5	2	4	3
4	2	7	2		18
5	3	8	15	6	2
6	2	#N/A	10	3	17
7	2	14	7	15	15
8	3	12	11	9	8
9	1	15	17	2	4
10	2	14	14	6	18
11	1	2	2	16	10
12	2	7	14	6	6
Sheet2

Cell Formulas
Range	Formula
G2:X2	G2	=TOROW(FILTER(B2:E12,A2:A12=2),3)
Dynamic array formulas.

 

[Rnkhch]

Wow, that's a fabulous function. At least on my home computer I don't have it, but I'll have to check my work computer tomorrow, and I'll update. Just in case I don't have it there either, do you have any other tricks?

Thanks much!

 

[Peter_SSs]

22 06 27.xlsm
	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	1	8	18	19	5		7	2	18	10	3	17	14	7	15	15	14	14	6	18	7	14	6	6
3	1	5	2	4	3
4	2	7	2		18
5	3	8	15	6	2
6	2	#N/A	10	3	17
7	2	14	7	15	15
8	3	12	11	9	8
9	1	15	17	2	4
10	2	14	14	6	18
11	1	2	2	16	10
12	2	7	14	6	6
Sheet2

Cell Formulas
Range	Formula
G2:X2	G2	=TRANSPOSE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IFERROR(FILTER(B2:E12,A2:A12=2)&"",""))&"</c></p>","//c"))
Dynamic array formulas.

 

[Rnkhch]

Awesome, thank you! Works really well. If I don't find TOROW, I'll mark this one as solution. I'll update.

 

[Rnkhch]

My work laptop doesn't have TOROW either yet, so for now I'll use your TRANSPOSE-based solution. Thanks again.

 

[Peter_SSs]

You're welcome. Thanks for the update.