User Profile
djclements
Silver Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: Excel
That's interesting. I'm using MS Edge on Windows 11, and I get a gear menu icon in the top-right corner of my screen, which allows me to edit a discussion I started... Glad to hear the edit worked for you with your reply, although it sounds like it might not be a reliable workaround 100% of the time as mentioned by SnowMan55. So disappointing to see what's become of this forum... it used to be my favorite one.Re: Need help with excel
Here's a couple additional options for MS365... Shift columns: =LET( tbl, A2:N6, rng, DROP(tbl,1), beg, INDEX(rng,,1), end, INDEX(rng,,2), val, DROP(rng,,2), low, MIN(beg), upp, MAX(end), fnλ, LAMBDA(b,e,(YEAR(e)-YEAR(b))*12+MONTH(e)-MONTH(b)), dur, fnλ(beg,end)+1, num, SEQUENCE(,fnλ(low,upp)+1,0), hdr, EOMONTH(low,num), cId, 1+num-fnλ(low,beg), rId, SEQUENCE(ROWS(rng)), arr, IF((cId>0)*(cId<=dur),INDEX(val,rId,cId),""), HSTACK(TAKE(tbl,,2),VSTACK(hdr,arr)) ) Unpivot/repivot: =LET( tbl, A2:N6, rng, DROP(tbl,1), beg, INDEX(rng,,1), end, INDEX(rng,,2), val, DROP(rng,,2), dur, (YEAR(end)-YEAR(beg))*12+MONTH(end)-MONTH(beg)+1, num, SEQUENCE(,COLUMNS(val),0), arr, IFS(num<dur,EOMONTH(+beg,num)), err, ISERROR(arr), rId, SEQUENCE(ROWS(rng)), pvt, PIVOTBY(TOCOL(IF(err,arr,rId),2),TOCOL(arr,2),TOCOL(IF(err,arr,val),2),SINGLE,0,0,,0), HSTACK(VSTACK(TAKE(tbl,1,2),INDEX(rng,TAKE(DROP(pvt,1),,1),{1,2})),DROP(pvt,,1)) ) See attached...Re: Issues attaching files to posts
mathetes I just had a file attachment removed after posting a reply here: Practical use of row delimiter with textsplit Immediately thereafter, though, I edited my reply and re-attached the same file (with the same filename consisting of underscores instead of spaces) and it worked. So, the filename doesn't appear to be the primary issue after all. 😏Re: Practical use of row_delimiter with TEXTSPLIT
The col_delimiter is used to spill text across columns, whereas the [row_delimiter] is used to spill text down rows. While the col_delimiter is technically a required argument, it can be omitted if the [row_delimiter] is provided. For example, =TEXTSPLIT("a,b,c",,",") will return {"a";"b";"c"} as a vertical vector. Use both delimiters to spill a 2D array of text. For example, =TEXTSPLIT("a,b;c,d",",",";") will return a 2 x 2 array consisting of {"a","b";"c","d"}. Use the optional [pad_with] argument to hide #N/A values, which result from a mismatched number of row and/or column delimiters in the text string (returning a jagged array). For example, =TEXTSPLIT("a,b;c",",",";",,,"") will return a 2 x 2 array consisting of {"a","b";"c",""}, which otherwise would have returned {"a","b";"c",#N/A} if the [pad_with] argument was not set. Knowing this, many users find the simple syntax of TEXTJOIN and TEXTSPLIT convenient and easy to understand and will use them together with either BYROW-FILTER or GROUPBY to handle certain data transformation scenarios and overcome the "nested arrays are not supported" issue. A typical example might look something like this: =LET( grp, GROUPBY(tblData[Team], tblData[Member], ARRAYTOTEXT, 0, 0), HSTACK(TAKE(grp,, 1), TEXTSPLIT(TEXTJOIN(";",, DROP(grp,, 1)), ", ", ";",,, "")) ) To be clear, there are many ways to accomplish this task, and this is not the method I would personally choose. While it's relatively short and easy to follow, it's also limited in the amount of data it can handle, due to TEXTJOIN's text limit of 32,767 characters. Having said that, it's a perfectly viable option for smaller datasets.Re: Issues attaching files to posts
Good catch mathetes ! If this indeed turns out to be the source of the file attachment issue that has plagued this forum of late, it seems like something that could easily be corrected by the developers. Standard URL encoding should convert spaces in filenames to %20. This would also explain why I haven't experienced this issue yet, when so many others have, as I typically use underscores instead of spaces when naming files that will be uploaded to the web. Hopefully they fix it, though, since major problems like this appear to be driving users and contributors away from this forum.Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers! REV_2025-10-16: updated function definitions for SCANBYROW and SCANBYCOL to be able to handle TYPE 16 and TYPE 128 values.Re: Countif/Countifs
Not sure why there wasn't any follow-up here, but you probably just need to change the system separator used in the static array for it to work with your regional and language settings (change the periods to commas or semi-colons), e.g. use {6,33,38} instead of {6.33.38}. INDIRECT will also work, if you're not opposed to its volatility: =SUM(COUNTIF(INDIRECT(C345),{6,33,38,45,46,51,52,56,60})) I hope that helps. Cheers!Re: Rack of Lambda
Harun24HR If you want to add more output options to the PERMA function, I think it would be best to ditch the two Boolean arguments, [combinations] and [without_repetitions], and replace them with a single [output_mode] argument. Please see the attached file for the alternative function definition and sample.Re: Rack of Lambda
UPDATE: I just added a handful of additional functions to my gist, as well as to the primary sample file attached to this discussion. These include CALENDARRAY, REPTROWS, REPTCOLS, REPTINTV and RANDOMIZE, as well as REDUCE3 and REDUCE5 to compliment SCAN3 and SCAN5. I've also attached a separate sample file for the CALENDARRAY function to demonstrate possible conditional formatting options for highlighting holidays/events on the calendar. Enjoy!Re: Rack of Lambda
Personally, I don't think a new function is warranted for the delimited values example, as you can simply combine UNPIVOT with TEXTTOCOLS to achieve the desired results. There was actually an example of this (albeit a bit hidden) in cell V3 of the InstanceNum worksheet in my original sample file. With this new example, though, you could try the following: =UNPIVOT(B4:C6,,TEXTTOCOLS(D4:D6,", "),,1) If you don't like that "ColumnID" is included in the final output, use CHOOSECOLS to remove it (see attached sample file). The second example, I would say, is more closely related to my REPTA function, or even the PMTSUMMARY function that we drafted yesterday. As a matter of fact, you can use PMTSUMMARY to achieve the desired results: =DROP(PMTSUMMARY(B14:D16,1,D14:D16,,1),,-1) However, this function is somewhat overkill for this particular scenario because all that's really needed is TOCOL-IF(S)-SEQUENCE with or without CHOOSEROWS: REPTROWS = LAMBDA(fields,number_times, CHOOSE( 1 + (COLUMNS(fields) = 1) + (TYPE(number_times) = 64) * 2, CHOOSEROWS(fields, TOCOL(IF(SEQUENCE(, number_times), SEQUENCE(ROWS(fields))))), TOCOL(IF(SEQUENCE(, number_times), fields)), CHOOSEROWS(fields, TOCOL(IFS(number_times >= SEQUENCE(, MAX(number_times)), SEQUENCE(ROWS(fields))), 2)), TOCOL(IFS(number_times >= SEQUENCE(, MAX(number_times)), fields), 2) ) ) The syntax would then simply be: =REPTROWS(B14:D16,D14:D16) Cheers!Re: Rack of Lambda
As a generalized function, perhaps something along these lines: PMTSUMMARY = LAMBDA(array,start_date,end_date,[interval],[int_type], LET( interval, IF(ISOMITTED(interval), 1, interval), int_type, IF(ISOMITTED(int_type), 2, int_type), a, IF(ISOMITTED(array), SEQUENCE(ROWS(start_date)), array), n, IF(int_type = 1, end_date - start_date, (YEAR(end_date) - YEAR(start_date)) * 12 + MONTH(end_date) - MONTH(start_date)), m, SEQUENCE(, AGGREGATE(14, 6, QUOTIENT(n, +interval), 1) + 1, 0) * IFS(interval > 0, interval), j, CHOOSE(int_type, start_date + m, EDATE(+start_date, m), EOMONTH(+start_date, m)), t, end_date >= j, λ, LAMBDA(x, TOCOL(IFS(t, x), 2)), HSTACK(IF(COLUMNS(a) = 1, λ(a), CHOOSEROWS(a, λ(SEQUENCE(ROWS(a))))), λ(j)) ) ) [interval] (optional) - the number of intervals between payments (default is 1) [int_type] (integer, optional) - specifies the interval type 1 - Days 2 - Months (default) 3 - Month Ends See attached examples...
