AHCLEAN

[Xlambda]

AHCLEAN Array Horizontal Clean, replaces errors with null strings and filters all the rows depending on nr. of blnks/null strings on each row. !!NEW!! BYROW

=LAMBDA(ar,[n],
    LET(a,IF(ISERROR(ar),"",IF(ar="","",ar)),
      x,BYROW(a,LAMBDA(a,SUM(--(a="")))),
      m,MIN(ABS(n),MAX(x)),
      FILTER(a,IF(n>=0,x<=m,x>=m))
    )
)

n=0 or omitted, filters only "full" rows sum(blnks)=0
n>0 filters the rows that have sum(blnks)<=n
n<0 filters only rows that have sum(blnks)>=abs( n)

LAMBDA 1.1.2.xlsx
	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	Z	AA	AB	AC	AD
1	sample										n>0	filters the rows that have sum(blnks)<=n
2	1	A	1	2	3	4	5	6	7		n=0	or omited, filters only "full" rows sum(blnks)=0
3	2	B	1	2	3	4	5	6			n<0	filters only rows that have sum(blnks)>=abs(n)
4	3	C	1	#N/A	3	4	5
5	4	D	1	2	3	4					n,4										n,-1
6	5	E	1	2	3						=AHCLEAN($A$2:$I$9,4)										=AHCLEAN($A$2:$I$9,-1)
7	6	F	1	2							1	A	1	2	3	4	5	6	7		2	B	1	2	3	4	5	6
8	7	G	1								2	B	1	2	3	4	5	6			3	C	1		3	4	5
9	8	H									3	C	1		3	4	5				4	D	1	2	3	4
10											4	D	1	2	3	4					5	E	1	2	3
11	n, 0 or omitted										5	E	1	2	3						6	F	1	2
12	=AHCLEAN($A$2:$I$9)																				7	G	1
13	1	A	1	2	3	4	5	6	7		n,5										8	H
14											=AHCLEAN($A$2:$I$9,5)
15	n,1										1	A	1	2	3	4	5	6	7		n,-2
16	=AHCLEAN($A$2:$I$9,1)										2	B	1	2	3	4	5	6			=AHCLEAN($A$2:$I$9,-2)
17	1	A	1	2	3	4	5	6	7		3	C	1		3	4	5				3	C	1		3	4	5
18	2	B	1	2	3	4	5	6			4	D	1	2	3	4					4	D	1	2	3	4
19											5	E	1	2	3						5	E	1	2	3
20	n,2										6	F	1	2							6	F	1	2
21	=AHCLEAN($A$2:$I$9,2)																				7	G	1
22	1	A	1	2	3	4	5	6	7		if n>clms(a) => whole array n,15										8	H
23	2	B	1	2	3	4	5	6			=AHCLEAN(A2:I9,9)
24											1	A	1	2	3	4	5	6	7		n,-5
25											2	B	1	2	3	4	5	6			=AHCLEAN($A$2:$I$9,-5)
26	n,3										3	C	1		3	4	5				6	F	1	2
27	=AHCLEAN($A$2:$I$9,3)										4	D	1	2	3	4					7	G	1
28	1	A	1	2	3	4	5	6	7		5	E	1	2	3						8	H
29	2	B	1	2	3	4	5	6			6	F	1	2
30	3	C	1		3	4	5				7	G	1								abs(n)>=clms(a)
31	4	D	1	2	3	4					8	H									=AHCLEAN($A$2:$I$9,-COLUMNS(A2:I9))
32																					8	H
33
AHCLEAN post 1

Cell Formulas
Range	Formula
D4	D4	=NA()
K6,U31,A27,U25,K23,A21,U16,A16,K14,A12,U6	K6	=FORMULATEXT(K7)
K7:S11	K7	=AHCLEAN($A$2:$I$9,4)
U7:AC13	U7	=AHCLEAN($A$2:$I$9,-1)
A13:I13	A13	=AHCLEAN($A$2:$I$9)
K15:S20	K15	=AHCLEAN($A$2:$I$9,5)
A17:I18	A17	=AHCLEAN($A$2:$I$9,1)
U17:AC22	U17	=AHCLEAN($A$2:$I$9,-2)
A22:I23	A22	=AHCLEAN($A$2:$I$9,2)
K24:S31	K24	=AHCLEAN(A2:I9,9)
U26:AC28	U26	=AHCLEAN($A$2:$I$9,-5)
A28:I31	A28	=AHCLEAN($A$2:$I$9,3)
U32:AC32	U32	=AHCLEAN($A$2:$I$9,-COLUMNS(A2:I9))
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	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	Z
1	sales report August for 5 products							Task 1: Filter only days with sales for all 5 products								Task 3: Filter days with no sales
2								(omitted<=>blnks<=0)								(means blnks>=5 or bigger number (n=-10)
3		P1	P2	P3	P4	P5		=AHCLEAN(A4:F26)								=AHCLEAN(A4:F26,-10)
4	01-08-21	67	78			20		09-08-21	35	71	37	87	14			05-08-21
5	02-08-21	35		42	35	35		15-08-21	44	71	76	34	31			18-08-21
6	03-08-21	81		53	68
7	04-08-21		62			45		Task 2: Filter the days with sales for at least 3 products								Task 4: Filter days with 3 prod. sales or less
8	05-08-21							(3 products means we have to filter rows with blnks<=(5-3)								(means blanks>=(5-3)							color "codes"
9	06-08-21	51	67	24				=AHCLEAN(A4:F26,2)								=AHCLEAN(A4:F26,-2)							1.) nr. blnks each row
10	07-08-21	84				82		01-08-21	67	78			20			01-08-21	67	78			20		2.) nr. prod on each row
11	08-08-21					38		02-08-21	35		42	35	35			03-08-21	81		53	68			3.) color
12	09-08-21	35	71	37	87	14		03-08-21	81		53	68				04-08-21		62			45
13	10-08-21				39	79		06-08-21	51	67	24					05-08-21							1	2	3
14	11-08-21	45	22	63				09-08-21	35	71	37	87	14			06-08-21	51	67	24				0	5
15	12-08-21	39	53		28	31		11-08-21	45	22	63					07-08-21	84				82		1	4
16	13-08-21	46		25				12-08-21	39	53		28	31			08-08-21					38		2	3
17	14-08-21	20		55	66	61		14-08-21	20		55	66	61			10-08-21				39	79		3	2
18	15-08-21	44	71	76	34	31		15-08-21	44	71	76	34	31			11-08-21	45	22	63				4	1
19	16-08-21			36	75	79		16-08-21			36	75	79			13-08-21	46		25				5	0
20	17-08-21	19	13			22		17-08-21	19	13			22			16-08-21			36	75	79
21	18-08-21							19-08-21	37	55		16	44			17-08-21	19	13			22		1.)+2.)=5
22	19-08-21	37	55		16	44		20-08-21	51	66	26	54				18-08-21
23	20-08-21	51	66	26	54			21-08-21	55		87		47			21-08-21	55		87		47
24	21-08-21	55		87		47		23-08-21	53	14			17			22-08-21				72	18
25	22-08-21				72	18										23-08-21	53	14			17
26	23-08-21	53	14			17		Task 5: Filter days with only 1 or 2 prod. sales
27								(means 3<=blnks<=4)								Task 6: Filter days that sold only 1 prod.
28	single cell report Task 5							=AHCLEAN(AHCLEAN(A4:F26,-3),4)								(means 4<=blnks<=4)
29	=AOVERLAP(H29#,B3:F3,-1,1)							04-08-21		62			45			=AHCLEAN(AHCLEAN(A4:F26,4),-4)
30		P1	P2	P3	P4	P5		07-08-21	84				82			08-08-21					38
31	04-08-21		62			45		08-08-21					38
32	07-08-21	84				82		10-08-21				39	79
33	08-08-21					38		13-08-21	46		25				other functions on minisheet
34	10-08-21				39	79		22-08-21				72	18		AOVERLAP
35	13-08-21	46		25
36	22-08-21				72	18
37
AHCLEAN post 2

Cell Formulas
Range	Formula
H3,P29,A29,H28,P9,H9,P3	H3	=FORMULATEXT(H4)
A4:A26	A4	=SEQUENCE(23,,"1-08-21")
H4:M5	H4	=AHCLEAN(A4:F26)
P4:U5	P4	=AHCLEAN(A4:F26,-10)
H10:M24	H10	=AHCLEAN(A4:F26,2)
P10:U25	P10	=AHCLEAN(A4:F26,-2)
H29:M34	H29	=AHCLEAN(AHCLEAN(A4:F26,-3),4)
A30:F36	A30	=AOVERLAP(H29#,B3:F3,-1,1)
P30:U30	P30	=AHCLEAN(AHCLEAN(A4:F26,4),-4)
Dynamic array formulas.

 

[Xlambda]

AVCLEAN Array Vertical Clean, replaces errors with null strings and filters all the clms depending on nr. of blnks/null strings on each clm. !!NEW!! BYCOL

=LAMBDA(ar,[n],
    LET(a,IF(ISERROR(ar),"",IF(ar="","",ar)),
       x,BYCOL(a,LAMBDA(a,SUM(--(a="")))),
       m,MIN(ABS(n),MAX(x)),
       FILTER(a,IF(n>=0,x<=m,x>=m))
    )
)

n>0 filters the clms that have sum(blnks)<=n
n=0 or omitted filters only "full" clms sum(blnks)=0
n<0 0 filters only clms that have sum(blnks)>=abs( n)
If abs( n) >= rws(a), n will be n=max(sum(blnks)) of each clm

LAMBDA 1.1.2.xlsx
	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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	sample								n,0 or omitted					n,1						n,2							n,3
2									=AVCLEAN(A3:G10)					=AVCLEAN(A3:G10,1)						=AVCLEAN(A3:G10,2)							=AVCLEAN(A3:G10,3)
3	P1	P2	P3	P4	P5	P6	P7		P1					P1						P1	P2	P3					P1	P2	P3	P4
4	1	2	3	4	5	6	7		1					1						1	2	3					1	2	3	4
5	1	2	3	4	5	6			1					1						1	2	3					1	2	3	4
6	1	#N/A	3	4	5				1					1						1		3					1		3	4
7	1	2	3	4					1					1						1	2	3					1	2	3	4
8	1	2	3						1					1						1	2	3					1	2	3
9	1	2							1					1						1	2						1	2
10	1								1					1						1							1
11
12										n,5								n,6								n,7 (n>=clms(a)) whole array
13	n>0	filters the clms that have sum(blnks)<=n								=AVCLEAN(A3:G10,5)								=AVCLEAN(A3:G10,6)								=AVCLEAN(A3:G10,7)
14	n=0	or omitted filters only "full" clms sum(blnks)=0								P1	P2	P3	P4	P5	P6			P1	P2	P3	P4	P5	P6	P7		P1	P2	P3	P4	P5	P6	P7
15	n<0	filters only clmss that have sum(blnks)>=abs(n)								1	2	3	4	5	6			1	2	3	4	5	6	7		1	2	3	4	5	6	7
16										1	2	3	4	5	6			1	2	3	4	5	6			1	2	3	4	5	6
17										1		3	4	5				1		3	4	5				1		3	4	5
18										1	2	3	4					1	2	3	4					1	2	3	4
19										1	2	3						1	2	3						1	2	3
20										1	2							1	2							1	2
21										1								1								1
22
23										n,-1							n,-2							n,-3						n,-7
24										=AVCLEAN(A3:G10,-1)							=AVCLEAN(A3:G10,-2)							=AVCLEAN(A3:G10,-3)						=AVCLEAN(A3:G10,-7)
25										P2	P3	P4	P5	P6	P7		P2	P3	P4	P5	P6	P7		P4	P5	P6	P7			P7
26										2	3	4	5	6	7		2	3	4	5	6	7		4	5	6	7			7
27										2	3	4	5	6			2	3	4	5	6			4	5	6
28											3	4	5					3	4	5				4	5
29										2	3	4					2	3	4					4
30										2	3						2	3
31										2							2
32
33
AVCLEAN post 1

Cell Formulas
Range	Formula
I2,N2,T2,X24,AD24,Q24,J24,Z13,R13,J13,AA2	I2	=FORMULATEXT(I3)
I3:I10	I3	=AVCLEAN(A3:G10)
N3:N10	N3	=AVCLEAN(A3:G10,1)
T3:V10	T3	=AVCLEAN(A3:G10,2)
AA3:AD10	AA3	=AVCLEAN(A3:G10,3)
J14:O21	J14	=AVCLEAN(A3:G10,5)
R14:X21	R14	=AVCLEAN(A3:G10,6)
Z14:AF21	Z14	=AVCLEAN(A3:G10,7)
J25:O32	J25	=AVCLEAN(A3:G10,-1)
Q25:V32	Q25	=AVCLEAN(A3:G10,-2)
X25:AA32	X25	=AVCLEAN(A3:G10,-3)
AD25:AD32	AD25	=AVCLEAN(A3:G10,-7)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	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	Z
1	August sales report 6 prod.								Task 1: what products					Task 2: out of prod. that register sales
2									register sales each day					filter products that have at least 1 day with no sales
3									n,0 or omitted					1st step: tot days 23, sum(blnks)<=22, n,22
4									=AVCLEAN(B5:F28)					=AVCLEAN(B5:G28,22)
5		P1	P2	P3	P4	P5	P6		P3	P5				P1	P3	P5	P6		2nd step,sum(blnks)>=1, n=-1
6	01-08-21	67		78		20	18		78	20				67	78	20	18		=AVCLEAN(N5#,-1)
7	02-08-21	35		42		35	19		42	35				35	42	35	19		P1	P6
8	03-08-21	81		53		32	52		53	32				81	53	32	52		67	18		single cell report Task 2
9	04-08-21			62		45	62		62	45					62	45	62		35	19
10	05-08-21			17		23	43		17	23					17	23	43		81	52			P1	P6
11	06-08-21	51		24		24	67		24	24				51	24	24	67			62		01-08-21	67	18
12	07-08-21	84		25		82	36		25	82				84	25	82	36			43		02-08-21	35	19
13	08-08-21			71		38			71	38					71	38			51	67		03-08-21	81	52
14	09-08-21	35		37		14	71		37	14				35	37	14	71		84	36		04-08-21		62
15	10-08-21			22		79	48		22	79					22	79	48					05-08-21		43
16	11-08-21	45		63		41	22		63	41				45	63	41	22		35	71		06-08-21	51	67
17	12-08-21	39		53		31	53		53	31				39	53	31	53			48		07-08-21	84	36
18	13-08-21	46		25		52	15		25	52				46	25	52	15		45	22		08-08-21
19	14-08-21	20		55		61	24		55	61				20	55	61	24		39	53		09-08-21	35	71
20	15-08-21	44		76		31	71		76	31				44	76	31	71		46	15		10-08-21		48
21	16-08-21			36		79	72		36	79					36	79	72		20	24		11-08-21	45	22
22	17-08-21	19		13		22	13		13	22				19	13	22	13		44	71		12-08-21	39	53
23	18-08-21			55		37	14		55	37					55	37	14			72		13-08-21	46	15
24	19-08-21	37		66		44	55		66	44				37	66	44	55		19	13		14-08-21	20	24
25	20-08-21	51		26		76	66		26	76				51	26	76	66			14		15-08-21	44	71
26	21-08-21	55		87		47	61		87	47				55	87	47	61		37	55		16-08-21		72
27	22-08-21			14		18	91		14	18					14	18	91		51	66		17-08-21	19	13
28	23-08-21	53		53		17	14		53	17				53	53	17	14		55	61		18-08-21		14
29																				91		19-08-21	37	55
30		Task 3: What products have no sales																	53	14		20-08-21	51	66
31		n, chosen as number abs(n)>=rows(a)																				21-08-21	55	61
32		=AHCLEAN(AVCLEAN(B5:F28,-32))																				22-08-21		91
33		P2	P4																			23-08-21	53	14
34	Note: if abs(n) excedes rows(a),n will take the value of max(sum(blnks)) bycol															=AOVERLAP(AVCLEAN(AVCLEAN(B5:G28,22),-1),A6:A28,1,-1)
35
36	other functions on minisheet
37	AOVERLAP
38
AVCLEAN post 2

Cell Formulas
Range	Formula
I4,B32,S6,N4	I4	=FORMULATEXT(I5)
I5:J28	I5	=AVCLEAN(B5:F28)
N5:Q28	N5	=AVCLEAN(B5:G28,22)
S7:T30	S7	=AVCLEAN(N5#,-1)
V10:X33	V10	=AOVERLAP(AVCLEAN(AVCLEAN(B5:G28,22),-1),A6:A28,1,-1)
B33:C33	B33	=AHCLEAN(AVCLEAN(B5:F28,-32))
P34	P34	=FORMULATEXT(V10)
Dynamic array formulas.