User Profile
djclements
Silver Contributor
Joined Oct 21, 2023
User Widgets
Recent Discussions
Re: Excel
Lorenzo Testing this strategy by attaching my files first, then typing all of this. Also testing a potential workaround for macro-enabled workbooks by renaming the file with a .zip extension (to be renamed and removed after downloading). If it works, there will be no further edits to this reply (2 files attached). EDIT: the 1st attachment (.xlsx) worked, but the 2nd attachment (.xlsm.zip) was purged. Looks like a promising strategy, but still subject to other "spam" filtering rules.Re: Excel
mathetes Many of the Excel forums that I frequent seem to be experiencing similar drops in the quantity (and quality) of questions being asked. I get the feeling that many users (especially those at an intermediate skill level) are turning to A.I. tools such as Chat GPT, Copilot, Agent Mode, etc. as their primary source of assistance nowadays. There used to be 1 or 2 questions each day that would spark my interest, but now it's more like 1 question every other week (which are typically in the form of a "challenge" shared by another contributor). I think we're on the verge of extinction, lol.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.
