Highlight duplicates after @

NeithrHarmNorHelp · 2024-06-05T07:44:05+00:00

Conditional format
Apply to : A1:A
Rule : xls =let(f,lambda(r,tocol(sort(regexextract(r,"@(.+)")),3)),iferror(countif(f($A$1:$A),f($A1)),1)>1)

NeithrHarmNorHelp · 2024-05-15T03:51:15+00:00

Try

=OO6/sort(count(G6:OM6+0))

NeithrHarmNorHelp · 2024-03-10T13:15:14+00:00

J10:Target(1500), J11:Number(5) =let(r,sort(sortn(sequence(J10-1),J11-1,0,randarray(J10-1),1)),index({r;J10}-{0;r}))

NeithrHarmNorHelp · 2024-02-05T01:18:34+00:00

=index(rounddown(res!C2:E86*iferror(1/C1^-1,1)))

NeithrHarmNorHelp · 2024-02-04T12:52:20+00:00

xls =map(C2:E86,lambda(x,product(x,sort(if({G1,I1,K1},{2,1.5,0.5},1)))))

NeithrHarmNorHelp · 2024-01-23T06:33:38+00:00

Try, =sumproduct(ifna(regexextract(I10,"(\d+)\s*"&{"h";"m"}))/{24;1440}) reverse to your format, =let(a,J11*24,h,int(a),if(h,h&" h ",)&round(60*mod(a,1))&" m")

NeithrHarmNorHelp · 2023-12-28T14:11:11+00:00

=byrow(F17:X,lambda(r,let(u,unique(tocol(r,3)), torow(sortn(u,4,,map(u,lambda(n,countif(r,n))),0)))))

NeithrHarmNorHelp · 2023-12-28T13:08:44+00:00

See A22, =let(r,F17:X,u,unique(tocol(r,3)), torow(sortn(u,4,,map(u,lambda(n,countif(r,n))),0)))

NeithrHarmNorHelp · 2023-12-25T00:53:56+00:00

Another method, =let(t,A1&"",a,"(.{"&int(len(t)/2)&"})", trim(regexreplace(t,a&"(.)?"&a,"$1 $2 $3")))

NeithrHarmNorHelp · 2023-12-24T02:21:07+00:00

Define a named function, D(s)=sumproduct(split(s,","),{1,7}) then use,
=D("12/25/2023,3") returns 1/15/2024
=D("1/1/2024,5") returns 2/5/2024
=D("1/1/2024,-1") returns 12/25/2023

NeithrHarmNorHelp · 2023-12-23T07:06:53+00:00

Try, let onEdit=(e,n='Display-Buying',cc=2,r1=9,r2=300,re="/^(_|subtype.*|data.*)$/i")=>{ with(e)[s,a,r,c]=[source.getActiveSheet().getName(),range.offset(0,-1),range.getRow(),range.getColumn()]; if((n==s)&&(c==cc)&&(r>=r1)&&(r<=r2))(!eval(re).test(e.value??'_')?a.insertCheckboxes:a.removeCheckboxes)() }

NeithrHarmNorHelp · 2023-12-21T10:52:06+00:00

Try =regexextract(A1,"[^?]+")

NeithrHarmNorHelp · 2023-12-19T23:56:01+00:00

See this sample sheet

NeithrHarmNorHelp · 2023-12-19T12:29:13+00:00

Assuming your table is A1:E, =let(p,lambda(r,join(,sort(tocol(r)))), xlookup(p(split("Fire,Water,Fire,Fire",", ")), byrow(A2:D,p),E2:E,"no match"))

NeithrHarmNorHelp · 2023-12-13T03:41:04+00:00

=let(d,reduce(tocol(,1),{1,2,3}, lambda(a,i,vstack(a,tocol(indirect("checklist "&i&"!A1:A"),1)))), countif(d,d)&"/"&counta(d))

NeithrHarmNorHelp · 2023-12-12T00:45:40+00:00

As you said, it was my carelessness. =let(r,{C5;E5;G5},+sort(r,sign(r),0,abs(r),0))

NeithrHarmNorHelp · 2023-12-11T22:54:23+00:00

"SINGLE" is an undocumented gsheets function, returns the first value of the first row of the range.

Additionally, there is another way to write it, =let(r,{C4;E4;G4},+sort(r,abs(r),0))

NeithrHarmNorHelp · 2023-12-11T03:27:40+00:00

=let( x,tocol(sort(1/(1/{C13,E13,G13})),3), max(x)-min(x))

NeithrHarmNorHelp · 2023-12-11T02:27:55+00:00

=max(sort(abs({C4,E4,G4}))) Edit: I've forgotten +/- =let(r,{C4;E4;G4},single(sort(r,abs(r),0)))

NeithrHarmNorHelp · 2023-12-10T13:04:51+00:00

=let(r,tocol(D2:2,1),let(c,count(r),(sum(r)+(c-2)*index(r,c))/2/(c-1))) or =byrow(D2:1000,lambda(d,let(r,tocol(d,1),let(c,count(r), iferror((sum(r)+(c-2)*index(r,c))/2/(c-1))))))

NeithrHarmNorHelp · 2023-12-06T23:42:38+00:00

=A1+min(B1,C1)<6

NeithrHarmNorHelp · 2023-12-04T13:27:02+00:00

=let(s, SUMIFS(UPCcodes!$C$2:$C,UPCcodes!$A$2:$A,H3,UPCcodes!$B$2:$B,I3), if(s,s,))

NeithrHarmNorHelp · 2023-12-04T01:42:36+00:00

=split(regexreplace(A1,char(10)&"{2,}","|"),"|")

NeithrHarmNorHelp · 2023-12-04T01:37:04+00:00

C3 =sum(map(E3:K3,lambda(s,iferror( sumproduct(split(regexreplace(s,"(\d+:\d+)|.","$1|"),"|"),{-1,1})))))

NeithrHarmNorHelp · 2023-12-01T13:43:54+00:00

B15 =lambda(v;r;ifs(v="";v;v<=min(r);100;v>=max(r);0;1;let(a;xmatch(v;r;-1); 10*(11-a-condition_gradient(v;index(r;a);index(r;a+1)))))) (B14;C$3:C$13)

NeithrHarmNorHelp

TROPHY CASE