all cases

=QUERY({B3:C15,ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN("*",1,REPT(FILTER(C3:C15,ISTEXT(C3:C15))&"*",FREQUENCY(SEQUENCE(ROWS(B3:B15)),FILTER(IF(B3:B15="",ROW(B3:B15)-3,),IF(B3:B15="",ROW(B3:B15)-3,))))),"*")))},"select Col1, sum(Col2) where Col1 is not null and Col2 is not null group by Col1 pivot Col3")

={"Fruits",TRANSPOSE(FILTER(C3:C15,ISTEXT(C3:C15)));{SORT(UNIQUE(QUERY(FILTER(B3:C15,ISNUMBER(C3:C15)),"select Col1"))),ARRAYFORMULA((IFERROR(VLOOKUP(SORT(UNIQUE(QUERY(FILTER(B3:C15,ISNUMBER(C3:C15)),"select Col1")))&TRANSPOSE(FILTER(C3:C15,ISTEXT(C3:C15))),{B3:B15&TRANSPOSE(SPLIT(TEXTJOIN("*",1,REPT(FILTER(C3:C15,ISTEXT(C3:C15))&"*",FREQUENCY(SEQUENCE(ROWS(B3:B15)),FILTER(IF(B3:B15="",ROW(B3:B15)-3,),IF(B3:B15="",ROW(B3:B15)-3,))))),"*")),C3:C15},2,0))))}}

| Unpivot

={"Date","Fruits","Qty";ARRAYFORMULA(SPLIT(QUERY(FLATTEN(""&B4:B5&","&C3:E3&","&C4:E5),"where Col1 !=''"),","))}

={"Date","Fruits","Qty";SORT(QUERY({QUERY(B4:C,"Select B,'"&C3&"',C where C is not null");QUERY(B4:D,"Select B,'"&D3&"',D where D is not null")},"Select * where Col1 is not null",0),1,0)}

={"Date","Fruits","Qty";

ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN(".",1,TO_TEXT(INDIRECT("A2:A"&COUNTA(A2:A)+1))&","&INDIRECT("B1:"&ADDRESS(1,COUNTA(B1:1)+1,4))&","&INDIRECT("B2:"&ADDRESS(COUNTA(A2:A)+1,COUNTA(B2:2)+1,4))),".")),","))}

| Sum by row

=ArrayFormula(SUMIF(IF(SEQUENCE(1,COUNTA(A1:C1)),SEQUENCE(COUNTA(A1:A10))),SEQUENCE(COUNTA(A1:A10)),A1:C10))

=TRANSPOSE(INDEX(QUERY(TRANSPOSE(A1:C),"select "&JOIN(",","sum(Col"&ROW(A1:A10)&")")),2))

=ArrayFormula(MMULT(A1:C10,SEQUENCE(COLUMNS(A1:C10))^0))

=BYROW(A1:C10,LAMBDA(row,SUM(row)))

| Accumulation

=ArrayFormula(IF(E2:E,MMULT(TRANSPOSE((ROW(E2:E)<=TRANSPOSE(ROW(E2:E)))*E2:E),E2:E^0),))

=ARRAYFORMULA({"Cumulative amount per account";IFERROR(1/DSUM(if((I1:I=TRANSPOSE(I2:I))*(ROW(I1:I)<=TRANSPOSE(ROW(I2:I))),L1:L,IFERROR(1/0)),SEQUENCE(ROWS(L2:L)),IF({1;1},))^-1)})

=ARRAYFORMULA(IF(B2:B,QUERY(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B+(ROW(B2:B)>TRANSPOSE(ROW(B2:B)))),"select Col"&JOIN("*Col",SEQUENCE(COUNT(B2:B)))&" label Col"&JOIN("*Col",SEQUENCE(COUNT(B2:B)))&"''"),))

| New row activation

=HYPERLINK("https://docs.google.com/spreadsheets/d/14ekc9RE4HNRkNPJXwds6RHuuWHRh7smDMAzAD8esEKU/edit#gid=1593169414&range=A"&ArrayFormula(MAX(ROW(A3:A)*(A3:A<>""))+1),"New row to write")

| Pareto

=ARRAYFORMULA(UNIQUE(QUERY(

ARRAY_CONSTRAIN(

{SORT(ArrayFormula(B2:B*100/SUM(B2:B)*0.8),1,0),

VLOOKUP(SORT(B2:B*100/SUM(B2:B)*0.8,1,0),

{B2:B*100/SUM(B2:B)*0.8,A2:A},2,0)

COUNTA(A2:A)*0.2,2),

"select Col2")))

| FIFO method

=ARRAYFORMULA(IF(A3:A="",,IFERROR(VLOOKUP(COUNTIFS(A3:A,A3:A,ROW(A3:A),"<="&ROW(A3:A))&A3:A,{COUNTIFS(FILTER(FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>""),FILTER(FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>""),SEQUENCE(COUNTA(FILTER(FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>""))),"<="&SEQUENCE(COUNTA(FILTER(FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>""))))&FILTER(FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(I3:I,I3:I<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>""),FILTER(FLATTEN(SPLIT(REPT(FILTER(K3:K,K3:K<>"")&"*",FILTER(J3:J,J3:J<>"")),"*")),FLATTEN(SPLIT(REPT(FILTER(K3:K,K3:K<>"")&"*",FILTER(J3:J,J3:J<>"")),"*"))<>"")},2,0),0)))

| Filter & ArrayFormula

=ArrayFormula(TRANSPOSE(REGEXREPLACE(TRIM(QUERY(IF(TRANSPOSE(D1:D3)=A1:A6,B1:B6,),,10^7)),"\s",", ")))

=MAP(D1:D3,LAMBDA(x,JOIN(", ",FILTER(B1:B,A1:A=x))))

| Sumifs & Arrayformula

=ARRAYFORMULA(IF(E:E="",,MMULT(IFNA(IF(REGEXMATCH(""&IF((D:D<>"")*(E:E<>"")*(E:E-D:D>=SEQUENCE(1,MAX(E:E-D:D+1),0,1)),D:D+SEQUENCE(1,MAX(E:E-D:D+1),0,1),),JOIN("|",""&UNIQUE(A1:A))),VLOOKUP(IF((D:D<>"")*(E:E<>"")*(E:E-D:D>=SEQUENCE(1,MAX(E:E-D:D+1),0,1)),D:D+SEQUENCE(1,MAX(E:E-D:D+1),0,1),),QUERY({A:B},"select Col1, sum(Col2) group by Col1"),2,0),),0),SEQUENCE(MAX(E:E-D:D+1))^0)))

=MAP(D1:D3,E1:E3,LAMBDA(d,e,SUM(FILTER(B1:B,A1:A>=d,A1:A<=e))))

| Sequence & ArrayFormula

=QUERY(FLATTEN(ARRAYFORMULA(IF((A:A <> "") * (B:B <> "") * (B:B - 1 >= SEQUENCE(1, MAX(B:B), 0, 1)), A:A + SEQUENCE(1, MAX(B:B), 0, 1),))), "WHERE Col1 IS NOT NULL", 0)

| Median & ArrayFormula

=ArrayFormula(VALUE(IF(ISEVEN(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")),(VALUE(REGEXEXTRACT(""&TRANSPOSE(SPLIT(TEXTJOIN("",,{IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),TRANSPOSE(SPLIT(REPT("-"&"@",COUNTA(B2:B3)),"@"))}),"-")),"^"&IFERROR(REPT(".",IF(ISEVEN(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")),COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2,ROUNDUP(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2))-1))&"(.)"))+VALUE(REGEXEXTRACT(""&TRANSPOSE(SPLIT(TEXTJOIN("",,{IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),TRANSPOSE(SPLIT(REPT("-"&"@",COUNTA(B2:B3)),"@"))}),"-")),"^"&IFERROR(REPT(".",IF(ISEVEN(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")),COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2,ROUNDUP(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2))-1))&".(.)")))/2,REGEXEXTRACT(""&TRANSPOSE(SPLIT(TEXTJOIN("",,{IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),TRANSPOSE(SPLIT(REPT("-"&"@",COUNTA(B2:B3)),"@"))}),"-")),"^"&IFERROR(REPT(".",IF(ISEVEN(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")),COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2,ROUNDUP(COUNTIFS(IF(TRANSPOSE(SEQUENCE(COUNTA(B2:E2))),SEQUENCE(COUNTA(B2:B3))),SEQUENCE(COUNTA(B2:B3)),IFERROR(IFNA(VLOOKUP(SEQUENCE(ROWS(B2:F3),COLUMNS(B2:E3)),{SEQUENCE(COUNTA(SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6))))),{SORT(FLATTEN(IF(B2:E3="","-",B2:E3+ROW(B2:B3)*10^6)))}},2,0))-ROW(B2:B3)*10^6),">0")/2))-1))&"(.)"))))

| Countuniqueifs & ArrayFormula

=ArrayFormula(IF(A2:A="",,COUNTIF(UNIQUE(FLATTEN(IFERROR(SPLIT(TRANSPOSE(REGEXREPLACE(TRIM(QUERY(IFNA(IF(TRANSPOSE(A2:A&H1)=A2:A&D2:D,A2:A&"^"&C2:C,)),,10^7)),"\s",",")),",")))),A2:A&"*")))

| Max & ArrayFormula

=TRANSPOSE(

INDEX(

QUERY(

TRANSPOSE({A2:C}),

"select "&JOIN(",","max(Col"&ROW(A2:A)-1&")")&"")

,2)

)

=BYROW(A2:C,LAMBDA(row,IF(MAX(row),MAX(row),)))

| Autofilling of empty rows

=ARRAYFORMULA(VLOOKUP(ROW(A1:A15),IF(LEN(A1:A15),{ROW(A1:A15),A1:A15},),2))

| Repeats

=ArrayFormula(QUERY(IFERROR(FLATTEN(SPLIT(MAP(A1:A,B1:B,LAMBDA(x,y,JOIN("^",IF(SEQUENCE(1,y),x)))),"^"))),"where Col1 is not null"))

| Maximum repeat value

=ArrayFormula(REGEXEXTRACT(BYROW(IF(B2:F10="",,COUNTIF(FLATTEN(IF(SEQUENCE(COLUMNS(B2:F10)),SEQUENCE(3))&B2:F10),SEQUENCE(3)&B2:F10))&"^"&B2:F10,LAMBDA(row,INDEX(SORT(TRANSPOSE(row),1,0),1))),".*\^(.*)"))

| Transforming a column to a 2D array

=ArrayFormula(VLOOKUP(SEQUENCE(COUNTA(A2:A)/5,COUNTA(A2:A)/3),{SEQUENCE(COUNTA(A2:A)),{FILTER(A2:A,A2:A<>"")}},2,0))

| Multi-level numbering

=ARRAYFORMULA(IF(A:A="",,A:A&" "&QUERY(ROW(A:A)-IFNA(VLOOKUP(ROW(A:A),IF(QUERY(FLATTEN(SPLIT(IF(A:A<>OFFSET(A:A,1,0),A:A&"~"&"^",A:A),"~")),"Where Col1 is not null",0)="^",ROW(A:A),),1),ROW(A1)-1),"where Col1 >0")))

=ARRAYFORMULA(IF(A:A = "",, A:A & ROW(A:A) + 1 - VLOOKUP(ROW(A:A), FILTER(ROW(A:A), A:A <> "", A:A <> {""; OFFSET(A:A,,,ROWS(A:A) - 1)}), 1)))

=ArrayFormula(

QUERY({A2:A,A2:A&"."&VLOOKUP(ROW(A2:A),

IF(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,"") = "",{"",""},

{ROW(A2:A),COUNTIFS(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),ROW(A2:A),"<="&ROW(A2:A))}),2,1)},

"select Col2 where Col1 is not null",0))

=ArrayFormula(COUNTIFS(E1:E12,"<>",ROW(E1:E12),"<="&ROW(E1:E12))&"."&COUNTIFS(COUNTIFS(E1:E12,"<>",ROW(E1:E12),"<="&ROW(E1:E12)),COUNTIFS(E1:E12,"<>",ROW(E1:E12),"<="&ROW(E1:E12)),ROW(E1:E12),"<="&ROW(E1:E12)))

={"number";ARRAYFORMULA(IFNA(VLOOKUP(A2:A&" "&B2:B&" "&C2:C;{TRANSPOSE(TRIM(QUERY(TRANSPOSE(QUERY(UNIQUE(SPLIT(FLATTEN(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2))&"^"&SPLIT(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2)))=A2:A&" "&B2:B;C2:C;);;10^7)));" "));"^"));"select Col1, Col2 where Col2 is not null order by Col1, Col2"));;10^7)))\COUNTIFS(QUERY(UNIQUE(SPLIT(FLATTEN(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2))&"^"&SPLIT(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2)))=A2:A&" "&B2:B;C2:C;);;10^7)));" "));"^"));"select Col1 where Col2 is not null order by Col1, Col2");QUERY(UNIQUE(SPLIT(FLATTEN(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2))&"^"&SPLIT(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(A2:B;A2:A)));;2)))=A2:A&" "&B2:B;C2:C;);;10^7)));" "));"^"));"select Col1 where Col2 is not null order by Col1, Col2");SEQUENCE(ROWS(UNIQUE(A2:C))-1);"<="&SEQUENCE(ROWS(UNIQUE(A2:C))-1))};2;0)))}

| Sequence with count

=ArrayFormula({ FLATTEN(SPLIT(FLATTEN(SPLIT(REPT(TRANSPOSE(QUERY(TRANSPOSE(VLOOKUP(SEQUENCE(14,2),{SEQUENCE(29,1,0,1),TRANSPOSE(QUERY(TRANSPOSE(IF(SEQUENCE(1,21),SORT(SEQUENCE(29,1,0,1),1,0))),,21))},2,0)),,2))&"^",14),"^"))," ")), FLATTEN(SPLIT(FLATTEN(TRANSPOSE(SPLIT(REPT(REPT({"A";"B";"C";"D";"E";"F";"G"}&" ",3)&" ",28*14)," ",0)))," ")), FLATTEN(SPLIT(FLATTEN(IF(SEQUENCE(1,14),TRIM(QUERY(FLATTEN(SPLIT(REPT(TRANSPOSE(QUERY(TRANSPOSE(IF(SEQUENCE(1,3),QUERY(SORT(SEQUENCE(42,1,97),1,0),"skipping 3")+SEQUENCE(1,3,0,1))),,3))&" ",14)," ")),,10^7))))," ")) })

| Cypher, using substitution of character

=ArrayFormula(JOIN("",HLOOKUP(SPLIT(REGEXREPLACE(C2,,"|"),"|"),SPLIT(REGEXREPLACE(B9:B10,,"|"),"|"),2,0),))

| 3 values according to highest rated column

=TRANSPOSE(INDEX(QUERY(QUERY(TRANSPOSE(SPLIT(FLATTEN(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(SORT(TRANSPOSE(A1:H11),1,0)),"offset 1")),,10^7)))," ")),"limit 3"),"select "&JOIN(",","sum(Col"&ROW(B2:B11)-1&")")),2))

| Adding up on original value

=ArrayFormula(MMULT(SEQUENCE(1,7)^0,D4:AJ10*D3:AJ3))

=INDEX(QUERY({D4:AJ10*D3:AJ3},"select "&JOIN(",","sum(Col"&SEQUENCE(COLUMNS(D4:AJ10))&")")),2)

| If cell contains text from list

=ArrayFormula(REGEXREPLACE(TRANSPOSE(TRIM(QUERY(TRANSPOSE(IFERROR(IF(REGEXMATCH(SPLIT(A2:A,", "),JOIN("|",FILTER(C2:C,C2:C<>""))),SPLIT(A2:A,", "),))),,10^7)))," ",", "))

| Reverse by the smallest

=ArrayFormula(IFERROR(BYROW(SPLIT(G2:G,"x"),LAMBDA(x,min(x)&"x"&max(x)))))

| Modified filter to find best match

=ArrayFormula(REGEXEXTRACT(BYROW(IF(SPLIT(BYROW(MAP(SPLIT(B3:B5,", ",0),LAMBDA(x,JOIN("|",IFNA(FILTER(D3:D6,REGEXMATCH(D3:D6,x)))))),LAMBDA(row,JOIN("|",row))),"|")="",,COUNTIF(FLATTEN(IF(SEQUENCE(COLUMNS(SPLIT(BYROW(MAP(SPLIT(B3:B5,", ",0),LAMBDA(x,JOIN("|",IFNA(FILTER(D3:D6,REGEXMATCH(D3:D6,x)))))),LAMBDA(row,JOIN("|",row))),"|"))),SEQUENCE(COUNTA(B3:B5)))&SPLIT(BYROW(MAP(SPLIT(B3:B5,", ",0),LAMBDA(x,JOIN("|",IFNA(FILTER(D3:D6,REGEXMATCH(D3:D6,x)))))),LAMBDA(row,JOIN("|",row))),"|")),SEQUENCE(COUNTA(B3:B5))&SPLIT(BYROW(MAP(SPLIT(B3:B5,", ",0),LAMBDA(x,JOIN("|",IFNA(FILTER(D3:D6,REGEXMATCH(D3:D6,x)))))),LAMBDA(row,JOIN("|",row))),"|")))&"^"&SPLIT(BYROW(MAP(SPLIT(B3:B5,", ",0),LAMBDA(x,JOIN("|",IFNA(FILTER(D3:D6,REGEXMATCH(D3:D6,x)))))),LAMBDA(row,JOIN("|",row))),"|"),LAMBDA(row,INDEX(SORT(TRANSPOSE(row),1,0),1))),".*\^(.*)"))

| Multiple row

=ArrayFormula(TRANSPOSE(REGEXREPLACE(TRIM(QUERY(IF(TRANSPOSE($A$18:$A$21&"^"&$B$18:$B$21&"^"&$C$18:$C$21&"^"&$D$18:$D$21)=$A$2:$A$6&"^"&$B$2:$B$6&"^"&$C$2:$C$6&"^"&$D$2:$D$6,TEXT(H2:H6,"DD/MM/YYY"),),,10^7)),"\s","|")))

| Empty rows between groups in auto-generated matrix

=ArrayFormula(IFERROR(SPLIT(FLATTEN({FILTER(E1:E,E1:E<>"")&"^"&TRANSPOSE(FILTER(F1:F,F1:F<>"")),IF(SEQUENCE(COUNTA(F1:F)),)}),"^")))

| Regexreplace URL to Root and Vlookup

=ArrayFormula(IFNA(VLOOKUP(REGEXEXTRACT(REGEXREPLACE(B3:B,"[^A-z]",""),TEXTJOIN("|",1,LOWER(REGEXREPLACE(Brands!A2:A," ","")))),{REGEXREPLACE(Brands!A2:A," ",""),Brands!A2:A},2,0)))

| Separating a table with two columns into multiple columns

=ArrayFormula(VLOOKUP(SEQUENCE(5,6),{SEQUENCE(COUNTA(FLATTEN(QUERY({A3:B},"select Col1, sum(Col2) where Col1 is not null group by Col1 order by sum(Col2) desc label sum(Col2) ''")))),FLATTEN(QUERY({A3:B},"select Col1, sum(Col2) where Col1 is not null group by Col1 order by sum(Col2) desc label sum(Col2) ''"))},2,0))

| Break specific cells out of partial duplicate rows

=ArrayFormula({""\TRANSPOSE(QUERY(UNIQUE(IFERROR(REGEXEXTRACT(B2:B;"[A-z]+")));"where Col1 is not null"));{UNIQUE(A2:A)\IFNA(VLOOKUP(UNIQUE(A2:A)&TRANSPOSE(QUERY(UNIQUE(IFERROR(REGEXEXTRACT(B2:B;"[A-z]+")));"where Col1 is not null"));{A2:A&IF(REGEXMATCH(""&B2:B;"\d+");B:B;)\B2:B};2;0))}})

| Conditional select columns MAX() and MIN()

={"Max Price Supplier","Min Price Supplier"; ArrayFormula({VLOOKUP(TRANSPOSE(INDEX(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(I3:I)=B3:B,F3:F,),,10^7)))," ")),"select "&JOIN(",","max(Col"&SEQUENCE(COUNTA(UNIQUE(B3:B)))&")")&""),2))&I3:I7,{F3:F&B3:B,C3:C},2,0),VLOOKUP(TRANSPOSE(INDEX(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(I3:I)=B3:B,F3:F,),,10^7)))," ")),"select "&JOIN(",","min(Col"&SEQUENCE(COUNTA(UNIQUE(B3:B)))&")")&""),2))&I3:I7,{F3:F&B3:B,C3:C},2,0)})}

| Average number of results per page of data imported

=ArrayFormula(AVERAGE(MMULT(--COUNTIF(QUERY({Scraper!B6:B,Scraper!B6:B&"."&VLOOKUP(ROW(Scraper!B6:B),IF(IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,"")="",{"",""},{ROW(Scraper!B6:B),COUNTIFS(IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,""),IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,""),ROW(Scraper!B6:B),"<="&ROW(Scraper!B6:B))}),2,1)},"select Col2 where Col1 is not null",0),SEQUENCE(1,7)&"."&SEQUENCE(3)),SEQUENCE(7)^0)/COUNTIFS(IF(SEQUENCE(1,7),SEQUENCE(3)),SEQUENCE(3),COUNTIF(QUERY({Scraper!B6:B,Scraper!B6:B&"."&VLOOKUP(ROW(Scraper!B6:B),IF(IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,"")="",{"",""},{ROW(Scraper!B6:B),COUNTIFS(IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,""),IF(Scraper!B6:B<>VLOOKUP(ROW(Scraper!B6:B)-1,{ROW(Scraper!B5:B),Scraper!B5:B},2,0),Scraper!B6:B,""),ROW(Scraper!B6:B),"<="&ROW(Scraper!B6:B))}),2,1)},"select Col2 where Col1 is not null",0),SEQUENCE(1,7)&"."&SEQUENCE(3)),">0")))

| Merge two rows with some overlapping data

=ArrayFormula({TRANSPOSE(SPLIT(QUERY(A3:A13,,10^7)," ")),UNIQUE(B3:C13),TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(UNIQUE(B3:B13&C3:C13))=B3:B13&C3:C13,D3:D13,),,10^7)))})

| Automatic Counting as per data

=ARRAYFORMULA(IF(A2:A="",,A2:A&COUNTIFS(A2:A,A2:A,QUERY({A2:A,A2:A&"."&VLOOKUP(ROW(A2:A),IF(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,"") = "",{"",""},{ROW(A2:A),COUNTIFS(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),ROW(A2:A),"<="&ROW(A2:A))}),2,1)},"select Col2",0),QUERY({A2:A,A2:A&"."&VLOOKUP(ROW(A2:A),IF(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,"") = "",{"",""},{ROW(A2:A),COUNTIFS(IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),IF(A2:A <> VLOOKUP(ROW(A2:A)-1,{ROW(A:A),A:A},2,0),A2:A,""),ROW(A2:A),"<="&ROW(A2:A))}),2,1)},"select Col2",0),ROW(A2:A),"<="&ROW(A2:A))))

| Compare Two Columns & Group Matches

=ArrayFormula(TRANSPOSE(TRIM(QUERY(IF(TRANSPOSE(D3:D)=INDEX(UNIQUE(A3:B),,1),INDEX(UNIQUE(A3:B),,2),),,10^7))))

| Custom rows and columns to query table

=ArrayFormula({{QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)};{"Grand total",MMULT(SEQUENCE(1,COUNTA(UNIQUE(B2:B)))^0,--QUERY({QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)}},"select Col2, Col3, Col4, Col5 offset 1",0))}},{IFERROR(INDEX({QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)};{"Grand total",MMULT(SEQUENCE(1,COUNTA(UNIQUE(B2:B)))^0,--QUERY({QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)}},"select Col2, Col3, Col4, Col5 offset 1",0))}},,3)/INDEX({QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)};{"Grand total",MMULT(SEQUENCE(1,COUNTA(UNIQUE(B2:B)))^0,--QUERY({QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C",1),{"Grand Total";MMULT(--QUERY(QUERY(A1:D19,"SELECT B, COUNT(A) WHERE A IS NOT NULL GROUP BY B PIVOT C"),"select Col2, Col3, Col4 offset 1",0),SEQUENCE(3)^0)}},"select Col2, Col3, Col4, Col5 offset 1",0))}},,5),"Completion rate")}})

| Match multiple columns and extract matching values to a new column with column info

=SORT({UNIQUE(INDEX(FILTER(SPLIT(FLATTEN(A2:D90&"^"&A1:D1),"^"),COUNTIF(FLATTEN(A2:D90),FLATTEN(A2:D90))>1),,1)),MAP(UNIQUE(INDEX(FILTER(SPLIT(FLATTEN(A2:D90&"^"&A1:D1),"^"),COUNTIF(FLATTEN(A2:D90),FLATTEN(A2:D90))>1),,1)),LAMBDA(x,JOIN(",",FILTER(INDEX(FILTER(SPLIT(FLATTEN(A2:D90&"^"&A1:D1),"^"),COUNTIF(FLATTEN(A2:D90),FLATTEN(A2:D90))>1),,2),INDEX(FILTER(SPLIT(FLATTEN(A2:D90&"^"&A1:D1),"^"),COUNTIF(FLATTEN(A2:D90),FLATTEN(A2:D90))>1),,1)=x))))})

Google Sites

Report abuse