AHCLEAN

Xlambda · Aug 24, 2021

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

Excel Formula:

=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

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

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

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 · Aug 24, 2021

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

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

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 · Aug 24, 2021

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

Excel Formula:

=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

3

P1

P2

P3

P4

P5

P6

P7

P1

P2

P3

P1

P2

P3

P4

4

1

2

3

4

5

6

7

1

2

3

1

2

3

4

5

1

2

3

4

5

6

1

2

3

1

2

3

4

6

1

#N/A

3

4

5

1

3

1

3

4

7

1

2

3

4

1

2

3

1

2

3

4

8

1

2

3

1

2

3

1

2

3

9

1

2

1

2

1

2

10

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

22

23

n,-1

n,-2

n,-3

n,-7

24

25

P3

P4

P5

P6

P7

P2

P3

P4

P5

P6

P7

P4

P5

P6

P7

26

2

3

4

5

6

7

2

3

4

5

6

7

4

5

6

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

30

2

3

2

3

31

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 · Aug 24, 2021

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

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

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

67

24

51

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

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

17-08-21

19

13

22

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

17

14

53

17

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.

AHCLEAN

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

AHCLEAN

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock