AHCLEAN

AHCLEAN(ar,[n])
ar
array
n
optional, integer

Replaces errors with null strings and filters all the rows depending on nr. of blnks/null strings on each row. !!NEW!! BYROW

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1samplen>0filters the rows that have sum(blnks)<=n
21A1234567n=0or omited, filters only "full" rows sum(blnks)=0
32B123456n<0filters only rows that have sum(blnks)>=abs(n)
43C1#N/A345
54D1234n,4n,-1
65E123=AHCLEAN($A$2:$I$9,4)=AHCLEAN($A$2:$I$9,-1)
76F121A12345672B123456
87G12B1234563C1345
98H3C13454D1234
104D12345E123
11n, 0 or omitted5E1236F12
12=AHCLEAN($A$2:$I$9)7G1
131A1234567n,58H
14=AHCLEAN($A$2:$I$9,5)
15n,11A1234567n,-2
16=AHCLEAN($A$2:$I$9,1)2B123456=AHCLEAN($A$2:$I$9,-2)
171A12345673C13453C1345
182B1234564D12344D1234
195E1235E123
20n,26F126F12
21=AHCLEAN($A$2:$I$9,2)7G1
221A1234567if n>clms(a) => whole array n,158H
232B123456=AHCLEAN(A2:I9,9)
241A1234567n,-5
252B123456=AHCLEAN($A$2:$I$9,-5)
26n,33C13456F12
27=AHCLEAN($A$2:$I$9,3)4D12347G1
281A12345675E1238H
292B1234566F12
303C13457G1abs(n)>=clms(a)
314D12348H=AHCLEAN($A$2:$I$9,-COLUMNS(A2:I9))
328H
33
AHCLEAN post 1
Cell Formulas
RangeFormula
D4D4=NA()
K6,U31,A27,U25,K23,A21,U16,A16,K14,A12,U6K6=FORMULATEXT(K7)
K7:S11K7=AHCLEAN($A$2:$I$9,4)
U7:AC13U7=AHCLEAN($A$2:$I$9,-1)
A13:I13A13=AHCLEAN($A$2:$I$9)
K15:S20K15=AHCLEAN($A$2:$I$9,5)
A17:I18A17=AHCLEAN($A$2:$I$9,1)
U17:AC22U17=AHCLEAN($A$2:$I$9,-2)
A22:I23A22=AHCLEAN($A$2:$I$9,2)
K24:S31K24=AHCLEAN(A2:I9,9)
U26:AC28U26=AHCLEAN($A$2:$I$9,-5)
A28:I31A28=AHCLEAN($A$2:$I$9,3)
U32:AC32U32=AHCLEAN($A$2:$I$9,-COLUMNS(A2:I9))
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1sales report August for 5 productsTask 1: Filter only days with sales for all 5 productsTask 3: Filter days with no sales
2(omitted<=>blnks<=0)(means blnks>=5 or bigger number (n=-10)
3P1P2P3P4P5=AHCLEAN(A4:F26)=AHCLEAN(A4:F26,-10)
401-08-2167782009-08-21357137871405-08-21
502-08-213542353515-08-21447176343118-08-21
603-08-21815368
704-08-216245Task 2: Filter the days with sales for at least 3 productsTask 4: Filter days with 3 prod. sales or less
805-08-21(3 products means we have to filter rows with blnks<=(5-3)(means blanks>=(5-3)color "codes"
906-08-21516724=AHCLEAN(A4:F26,2)=AHCLEAN(A4:F26,-2)1.) nr. blnks each row
1007-08-21848201-08-2167782001-08-216778202.) nr. prod on each row
1108-08-213802-08-213542353503-08-218153683.) color
1209-08-21357137871403-08-2181536804-08-216245
1310-08-21397906-08-2151672405-08-21123
1411-08-2145226309-08-21357137871406-08-2151672405
1512-08-213953283111-08-2145226307-08-21848214
1613-08-21462512-08-213953283108-08-213823
1714-08-212055666114-08-212055666110-08-21397932
1815-08-21447176343115-08-21447176343111-08-2145226341
1916-08-2136757916-08-2136757913-08-21462550
2017-08-2119132217-08-2119132216-08-21367579
2118-08-2119-08-213755164417-08-211913221.)+2.)=5
2219-08-213755164420-08-215166265418-08-21
2320-08-215166265421-08-2155874721-08-21558747
2421-08-2155874723-08-2153141722-08-217218
2522-08-21721823-08-21531417
2623-08-21531417Task 5: Filter days with only 1 or 2 prod. sales
27(means 3<=blnks<=4)Task 6: Filter days that sold only 1 prod.
28single cell report Task 5=AHCLEAN(AHCLEAN(A4:F26,-3),4)(means 4<=blnks<=4)
29=AOVERLAP(H29#,B3:F3,-1,1)04-08-216245=AHCLEAN(AHCLEAN(A4:F26,4),-4)
30 P1P2P3P4P507-08-21848208-08-2138
3104-08-21624508-08-2138
3207-08-21848210-08-213979
3308-08-213813-08-214625other functions on minisheet
3410-08-21397922-08-217218AOVERLAP
3513-08-214625
3622-08-217218
37
AHCLEAN post 2
Cell Formulas
RangeFormula
H3,P29,A29,H28,P9,H9,P3H3=FORMULATEXT(H4)
A4:A26A4=SEQUENCE(23,,"1-08-21")
H4:M5H4=AHCLEAN(A4:F26)
P4:U5P4=AHCLEAN(A4:F26,-10)
H10:M24H10=AHCLEAN(A4:F26,2)
P10:U25P10=AHCLEAN(A4:F26,-2)
H29:M34H29=AHCLEAN(AHCLEAN(A4:F26,-3),4)
A30:F36A30=AOVERLAP(H29#,B3:F3,-1,1)
P30:U30P30=AHCLEAN(AHCLEAN(A4:F26,4),-4)
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1samplen,0 or omittedn,1n,2n,3
2=AVCLEAN(A3:G10)=AVCLEAN(A3:G10,1)=AVCLEAN(A3:G10,2)=AVCLEAN(A3:G10,3)
3P1P2P3P4P5P6P7P1P1P1P2P3P1P2P3P4
41234567111231234
5123456111231234
61#N/A3451113134
71234111231234
812311123123
912111212
1011111
11
12n,5n,6n,7 (n>=clms(a)) whole array
13n>0filters the clms that have sum(blnks)<=n=AVCLEAN(A3:G10,5)=AVCLEAN(A3:G10,6)=AVCLEAN(A3:G10,7)
14n=0or omitted filters only "full" clms sum(blnks)=0P1P2P3P4P5P6P1P2P3P4P5P6P7P1P2P3P4P5P6P7
15n<0filters only clmss that have sum(blnks)>=abs(n)12345612345671234567
16123456123456123456
17134513451345
18123412341234
19123123123
20121212
21111
22
23n,-1n,-2n,-3n,-7
24=AVCLEAN(A3:G10,-1)=AVCLEAN(A3:G10,-2)=AVCLEAN(A3:G10,-3)=AVCLEAN(A3:G10,-7)
25P2P3P4P5P6P7P2P3P4P5P6P7P4P5P6P7P7
2623456723456745677
272345623456456
2834534545
292342344
302323
3122
32
33
AVCLEAN post 1
Cell Formulas
RangeFormula
I2,N2,T2,X24,AD24,Q24,J24,Z13,R13,J13,AA2I2=FORMULATEXT(I3)
I3:I10I3=AVCLEAN(A3:G10)
N3:N10N3=AVCLEAN(A3:G10,1)
T3:V10T3=AVCLEAN(A3:G10,2)
AA3:AD10AA3=AVCLEAN(A3:G10,3)
J14:O21J14=AVCLEAN(A3:G10,5)
R14:X21R14=AVCLEAN(A3:G10,6)
Z14:AF21Z14=AVCLEAN(A3:G10,7)
J25:O32J25=AVCLEAN(A3:G10,-1)
Q25:V32Q25=AVCLEAN(A3:G10,-2)
X25:AA32X25=AVCLEAN(A3:G10,-3)
AD25:AD32AD25=AVCLEAN(A3:G10,-7)
Dynamic array formulas.
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1August sales report 6 prod.Task 1: what productsTask 2: out of prod. that register sales
2register sales each dayfilter products that have at least 1 day with no sales
3n,0 or omitted1st step: tot days 23, sum(blnks)<=22, n,22
4=AVCLEAN(B5:F28)=AVCLEAN(B5:G28,22)
5P1P2P3P4P5P6P3P5P1P3P5P62nd step,sum(blnks)>=1, n=-1
601-08-2167782018782067782018=AVCLEAN(N5#,-1)
702-08-2135423519423535423519P1P6
803-08-21815332525332815332526718single cell report Task 2
904-08-2162456262456245623519
1005-08-2117234317231723438152 P1P6
1106-08-21512424672424512424676201-08-216718
1207-08-21842582362582842582364302-08-213519
1308-08-21713871387138516703-08-218152
1409-08-2135371471371435371471843604-08-2162
1510-08-21227948227922794805-08-2143
1611-08-2145634122634145634122357106-08-215167
1712-08-21395331535331395331534807-08-218436
1813-08-2146255215255246255215452208-08-21
1914-08-2120556124556120556124395309-08-213571
2015-08-2144763171763144763171461510-08-2148
2116-08-213679723679367972202411-08-214522
2217-08-2119132213132219132213447112-08-213953
2318-08-2155371455375537147213-08-214615
2419-08-2137664455664437664455191314-08-212024
2520-08-21512676662676512676661415-08-214471
2621-08-2155874761874755874761375516-08-2172
2722-08-211418911418141891516617-08-211913
2823-08-2153531714531753531714556118-08-2114
299119-08-213755
30Task 3: What products have no sales531420-08-215166
31n, chosen as number abs(n)>=rows(a)21-08-215561
32=AHCLEAN(AVCLEAN(B5:F28,-32))22-08-2191
33P2P423-08-215314
34Note: 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
36other functions on minisheet
37AOVERLAP
38
AVCLEAN post 2
Cell Formulas
RangeFormula
I4,B32,S6,N4I4=FORMULATEXT(I5)
I5:J28I5=AVCLEAN(B5:F28)
N5:Q28N5=AVCLEAN(B5:G28,22)
S7:T30S7=AVCLEAN(N5#,-1)
V10:X33V10=AOVERLAP(AVCLEAN(AVCLEAN(B5:G28,22),-1),A6:A28,1,-1)
B33:C33B33=AHCLEAN(AVCLEAN(B5:F28,-32))
P34P34=FORMULATEXT(V10)
Dynamic array formulas.
 

Forum statistics

Threads
1,223,947
Messages
6,175,563
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top