ACLEAN

[Xlambda]

ACLEAN cleans an array, replaces errors with null strings or removes the rows with errors or removes the rows with errors and blanks. calls AUNIQUE

=LAMBDA(a,k,
    LET(xk,OR(k={0,1,2}),
       r,ROWS(a),sr,SEQUENCE(r),
       x,ISERROR(a)*sr,y,sr*IFERROR(x+(a=""),1),
       z,AUNIQUE(SWITCH(k,0,0,1,x,2,y),),
       xm,ISNA(XMATCH(sr,z)),
       IF(xk,IFERROR(FILTER(IF(a="","",a),xm),""),"0 null strings for errors,1 removes errors only,2 removes errors and blanks")
    )
)

LAMBDA 5.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	16	31	46	61		k=0	1	16	31	46	61
2	2	17	32	#DIV/0!	62		null str.	2	17	32		62
3	3		33	48	63		for	3		33	48	63
4	4	19	34	49	64		errors	4	19	34	49	64
5	5	20	35		65			5	20	35		65
6	6	#DIV/0!	36	51	66			6		36	51	66
7	7	22	37	52	67			7	22	37	52	67
8	8	23			68			8	23			68
9	9	24	39	54	69			9	24	39	54	69
10	10	25	40	55	#DIV/0!			10	25	40	55
11	11	26	#DIV/0!	56	71			11	26		56	71
12	12		42	57	72			12		42	57	72
13	13	28	43	58	73			13	28	43	58	73
14	14	#DIV/0!		#DIV/0!	74			14				74
15	15	30	45	60	75			15	30	45	60	75
16
17	k=1	rmvs	errors	only				k=2	rmvs	errs	and	blnks
18	1	16	31	46	61			1	16	31	46	61
19	3		33	48	63			4	19	34	49	64
20	4	19	34	49	64			7	22	37	52	67
21	5	20	35		65			9	24	39	54	69
22	7	22	37	52	67			13	28	43	58	73
23	8	23			68			15	30	45	60	75
24	9	24	39	54	69
25	12		42	57	72
26	13	28	43	58	73
27	15	30	45	60	75
28
ACLEAN post

Cell Formulas
Range	Formula
H1:L15	H1	=ACLEAN(A1:E15,)
D2,D14,B14,C11,E10,B6	D2	=1/0
A18:E27	A18	=ACLEAN(A1:E15,1)
H18:L23	H18	=ACLEAN(A1:E15,2)
Dynamic array formulas.