User Profile
SergeiBaklan
Diamond Contributor
Joined 10 years ago
User Widgets
Recent Discussions
Re: Excel Filter Formula Help
If nothing matches FILTER returns single text value. Applying CHOOSECOLS(..., 3,4,8,9) to single value returns #VALUE! error - formulae simply can't find columns listed. Only CHOOSECOLS(..., 1) works if no matches. Alternatively you may use something like =IFERROR( CHOOSECOLS( FILTER(...), ....), "nothing was found" )Re: Excel formula to determine missing numbers
As variant =LET( data, Tabelle1[Case number], cases, FILTER( data, ISNUMBER( --LEFT(data,4) ) ), pivot, PIVOTBY(RIGHT(cases,3), LEFT(cases,4),RIGHT(cases,3)*1, SUM,,0,,0), ids, DROP(TAKE(pivot,,1),1), cols, DROP(SEQUENCE(COLUMNS(pivot)), 1), missed, REDUCE("Missed", cols, LAMBDA(a,v, VSTACK( a, LET( col, CHOOSECOLS(pivot,v), colIds, DROP(col,1), colYear, TAKE(col,1), TOCOL(TAKE( IF( colIds="", colYear & "-" & ids, NA()), XMATCH("\d",colIds,3,-1) ),3) ) ) ) ), missed ) taking OliverScheurich file as the sample Power Query also work if to clean the data first, e.g. to remove errors and nulls after transforming left part to years.Re: SEQUENCE formula with curly brackets
Alternatively =LET( data, ROW(E1:E2288)/(E1:E2288=""), start, AGGREGATE(15,6,data, G1), end, AGGREGATE(15,6,data, G1+1), IFERROR( SEQUENCE(end-start,,start), "G1 is too big" ) ) or =LET( data, TOCOL(ROW(E1:E2288)/(E1:E2288=""),3), x, @CHOOSEROWS(data, G1), y, @CHOOSEROWS(data, G1+1), IFERROR( SEQUENCE(y-x,,x), "G1 is too big" ) )Re: I need to learn how to use the LET function in Excel...
IMHO, it all depends on concrete case. Most important points to consider are maintenance and performance. From maintenance point of view LET(), IFS(), SWITCH() and their combinations are more preferrable. But if performance is critical when nested IF is better choice.Re: REGEX+TEXTSPLIT
If without regex we may use RepSplit=LAMBDA(txt, LET( sep, "|", engine, LAMBDA(fn,str, IF(LEN(str) <= 1, str, LET( h, LEFT(str), t, RIGHT(str, LEN(str) - 1), h & IF(h=LEFT(t), sep, "") & fn(fn,t) )) ), rs, LAMBDA(str, engine(engine, str)), TEXTSPLIT( rs(txt), sep ) ) ); With Regex we may use peiyezhu pattern inside Excel =TEXTSPLIT(REGEXREPLACE(A2, "(.)(?=\1)", "\1|"), "|") Results are
