User Profile
djclements
Bronze Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Re: Additional help needed with existing formula using LAMDA- Excel 365
It's not likely to be related to the other tabs/worksheets in your master workbook, as the formula is only referencing cells/ranges in the 'RESULTS' tab directly. The INDIRECT function is being used to reference tables in the workbook, but the syntax for this is the same as what was already working in your original sample workbook(s), so that wouldn't explain it. I saw your comment on the previous thread, where you confirmed "The master workbook is named differently and contains more tabs than the redacted (included here) spreadsheet, but all of the table names and column headers are the same.", so there shouldn't be any issues when copying the formula over. However, I also noticed there was a bit of a struggle to get any of the suggested formulas on that thread to work, and a considerable amount of time was spent troubleshooting this. To transfer the formula from the sample workbook to your master workbook, highlight the entire formula from within the formula bar in the sample workbook and press Ctrl+C to copy, then select the destination cell in your master workbook, click into the formula bar (delete any previous formula, if applicable) and press Ctrl+V to paste. #VALUE! errors can be difficult to track down, but it usually means there is an issue with one or more of the data types used. For example, =10+"text" will return this error because the addition operator is expecting two numbers (type 1) but received a text string (type 2). =TAKE(A1:A5,{3}) will also error because the rows argument is expecting a scalar (type 1) but received an array (type 64). I've reviewed my formula and don't see anything out of place. Even if there was, it wouldn't explain why the formula works in the sample workbook but not in the master workbook. Having said that, I'm not really a fan of how the current "working" formula outputs the report. There's a different number of columns being selected from each table, and the field order is different between tables. The end result is a bit of a mess, where only the first 3 columns are identical across all 8 tables, and the rest are a mix-match of data types. This makes formatting dates virtually impossible, as fields like "Date Processed" and "Date Reconsidered" are appearing in the same columns as numeric fields like "PID" and "Issue Number". Another option is to use something of a field-mapping table to link compatible fields and output them to the same column. For example: =LET( _rep, RESULTS!$A$2, _beg, RESULTS!$B$2, _end, RESULTS!$C$2, _lst, RESULTS!$A$5:$A$12, _inc, (RESULTS!$E$5:$E$12="EXCEED")+(RESULTS!$E$5:$E$12="NOT MET"), IF( NOT(OR(_inc)), "[no records found]", LET( VALλ, LAMBDA(lbl,tId,csv,LAMBDA(x,CHOOSE(x,lbl,tId,csv))), EXPλ, LAMBDA(array,[rws],EXPAND(array,rws,9,"")), INCλ, LAMBDA(field3,field2,(field3=_rep)*(field2>=_beg)*(field2<=_end)), _fId, SEQUENCE(,9), _cId, VSTACK("1,2,3,4,,,5,13,14", "1,2,3,4,,5,6,10,11", "1,2,3,4,,,5,11,12", "1,2,3,4,5,,,7,8", "1,2,3,4,,,5,11,12", "1,2,3,5,7,6,4,12,13", "1,2,3,4,5,,6,14,15", "1,2,3,4,5,6,7,13,18"), REDUCE( EXPλ("Field"&_fId,2), FILTER(MAP(_lst,SEQUENCE(ROWS(_lst)),_cId,VALλ),_inc), LAMBDA(acc,val, LET( tbl, INDIRECT("tbl"&SUBSTITUTE(SUBSTITUTE(val(1)," ",),"-",)&"[#ALL]"), arr, SORTBY(EXPλ(CHOOSECOLS(tbl,--TEXTSPLIT(val(3),",",,1))),SORTBY(_fId,TEXTSPLIT(val(3),",")="")), bdy, DROP(arr,1), qry, FILTER(bdy,INCλ(CHOOSECOLS(bdy,3),CHOOSECOLS(bdy,2))), VSTACK(acc,EXPλ("["&val(2)&"] "&val(1)&":"),EXPλ(TAKE(arr,1)),EXPλ(IF(ISBLANK(qry),"",qry),ROWS(qry)+1)) ) ) ) ) ) ) The width of the output array in this example is fixed (9 columns). _cId is where the field-mapping is set for each table, and arr is where the field-mapping is applied (using SORTBY-TEXTSPLIT). This will output numeric fields like "PID" and "Issue Number" in Field5, and all date fields in Field7, making them easier to format: Furthermore, if you're always dealing with the same 8 tables, it's also possible to eliminate the INDIRECT function by utilizing a custom CHOOSE based lambda function to select each table as needed. For example: =LET( _rep, RESULTS!$A$2, _beg, RESULTS!$B$2, _end, RESULTS!$C$2, _lst, RESULTS!$A$5:$A$12, _inc, (RESULTS!$E$5:$E$12="EXCEED")+(RESULTS!$E$5:$E$12="NOT MET"), IF( NOT(OR(_inc)), "[no records found]", LET( VALλ, LAMBDA(lbl,tId,csv,LAMBDA(x,CHOOSE(x,lbl,tId,csv))), EXPλ, LAMBDA(array,[rws],EXPAND(array,rws,9,"")), INCλ, LAMBDA(field3,field2,(field3=_rep)*(field2>=_beg)*(field2<=_end)), TBLλ, LAMBDA(table_id,CHOOSE(table_id, tblClaims[#All],tblMiscAssignments[#All],tblProcessProtest[#All],tblIDD[#All], tblProcessAppeal[#All],tblDPU[#All],tblVetRRVDVOP[#All],tblRESEA[#All])), _fId, SEQUENCE(,9), _cId, VSTACK("1,2,3,4,,,5,13,14", "1,2,3,4,,5,6,10,11", "1,2,3,4,,,5,11,12", "1,2,3,4,5,,,7,8", "1,2,3,4,,,5,11,12", "1,2,3,5,7,6,4,12,13", "1,2,3,4,5,,6,14,15", "1,2,3,4,5,6,7,13,18"), REDUCE( EXPλ("Field"&_fId,2), FILTER(MAP(_lst,SEQUENCE(ROWS(_lst)),_cId,VALλ),_inc), LAMBDA(acc,val, LET( tbl, TBLλ(val(2)), arr, SORTBY(EXPλ(CHOOSECOLS(tbl,--TEXTSPLIT(val(3),",",,1))),SORTBY(_fId,TEXTSPLIT(val(3),",")="")), bdy, DROP(arr,1), qry, FILTER(bdy,INCλ(CHOOSECOLS(bdy,3),CHOOSECOLS(bdy,2))), VSTACK(acc,EXPλ("["&val(2)&"] "&val(1)&":"),EXPλ(TAKE(arr,1)),EXPλ(IF(ISBLANK(qry),"",qry),ROWS(qry)+1)) ) ) ) ) ) ) TBLλ is the lambda function that contains the references to each table, and TBLλ(val(2)) is where each table is recalled (replacing the INDIRECT function). I've also used a custom VALλ function here to make referencing the variables easier (replacing the INDEX function). Please note, many of the variables and functions used in these examples can be defined in Name Manager, which would make the end formula shorter and easier to write. However, I refrained from doing so to make it as easy as possible for you to transfer the formula to your master workbook. If you're still experiencing issues and want to send me a copy of your master workbook in a private message (go to my profile page and click "Message"), I'll be happy to troubleshoot it for you. Cheers!Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
Interesting concept, passing the entire table as the first and only required argument, then unpivoting based on the optional arguments provided. Definitely looks doable as a lambda function. I have a couple of custom functions already that I use for basic unpivots. The first one automatically removes blanks and errors, which is the most common request: =LAMBDA(row_fields,col_labels,values,[scan_by_col], LET( tst, values<>"", fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2,scan_by_col)), HSTACK( CHOOSEROWS(row_fields,fnλ(SEQUENCE(ROWS(row_fields)))), CHOOSEROWS(TRANSPOSE(col_labels),fnλ(SEQUENCE(,COLUMNS(col_labels)))), fnλ(values) ) ) ) Set the optional [scan_by_col] argument to 1 or TRUE to change the output order. The second one has the option to remove blanks and errors as desired: =LAMBDA(row_fields,col_labels,values,[ignore],[scan_by_col], LET( v, TOCOL(values,,scan_by_col), a, HSTACK( INDEX(row_fields,TOCOL(IFNA(SEQUENCE(ROWS(row_fields)),values),,scan_by_col),SEQUENCE(,COLUMNS(row_fields))), INDEX(col_labels,SEQUENCE(,ROWS(col_labels)),TOCOL(IFNA(SEQUENCE(,COLUMNS(col_labels)),values),,scan_by_col)),v), CHOOSE(ignore+1,a,FILTER(a,NOT(ISBLANK(v))),FILTER(a,NOT(ISERROR(v))),FILTER(a,NOT(ISBLANK(v)+ISERROR(v)))) ) ) The optional [ignore] argument accepts the same choices as that of TOCOL/TOROW: 0 - Keep all values (default) 1 - Ignore blanks 2 - Ignore errors 3 - Ignore blanks and errors Both methods can handle multiple columns of row_fields, as well as multiple rows of col_labels. In most cases I'm not too worried about headers as they're pretty easy to input/stack manually and customize on your own. If you wanted to get cute, though, and include an option for headers, it could look something like this: =LAMBDA(row_fields,col_labels,values,[scan_by_col],[headers], LET( tst, values<>"", fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2,scan_by_col)), pvt, HSTACK( CHOOSEROWS(row_fields,fnλ(SEQUENCE(ROWS(row_fields)))), CHOOSEROWS(TRANSPOSE(col_labels),fnλ(SEQUENCE(,COLUMNS(col_labels)))), fnλ(values) ), IF( TYPE(headers)=64, VSTACK(headers,pvt), IF( headers, VSTACK(HSTACK("Field"&SEQUENCE(,COLUMNS(row_fields)),"Attribute"&SEQUENCE(,ROWS(col_labels)),"Value"),pvt), pvt ) ) ) ) The optional [headers] argument would then accept the following: 0 or FALSE - None (default) 1 or TRUE - Auto-generate A horizontal array of custom headers, e.g. {"first","last","attribute","value"} For example: =Unpivotλ(A3:B5,C1:H2,C3:H5,TRUE,{"First","Last","Fiscal Year","Month","Commissions"}) More advanced scenarios involving multiple value fields (e.g. Sales and Commissions) can get kind of hairy, so I just end up writing tailored solutions on a case-by-case basis for those situations.Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
Well done! I've been doing a lot of table transformations with INDEX-SEQUENCE algorithms recently, so I took a crack at it: =LAMBDA(ref_3d, LET( a, HSTACK(ref_3d), b, TAKE(a,1), h, ROWS(a)-1, w, COLUMNS(a), m, SHEETS(ref_3d), n, w/m, k, SEQUENCE(h*m,,0), x, DROP(SORTBY(a,QUOTIENT(SEQUENCE(,w,0),n),1,b,1),1), y, INDEX(x,MOD(k,h)+1,QUOTIENT(k,h)*n+SEQUENCE(,n)), VSTACK(UNIQUE(SORT(b,,,1),1),FILTER(y,TAKE(y,,1)<>"")) ) ) It's a little longer, but should hold up well performance-wise. If any one of the worksheets were missing a field, though, I think an unpivot-repivot method might be the way to go. Something like: =LET( a, HSTACK(Albums3D), b, DROP(a,1), w, COLUMNS(a), i, SEQUENCE(ROWS(b)), j, QUOTIENT(SEQUENCE(,w,0),w/SHEETS(Albums3D)), t, b<>"", f, LAMBDA(x,TOCOL(IFS(t,x),2)), DROP(PIVOTBY(HSTACK(f(j),f(i)),f(TAKE(a,1)),f(b),SINGLE,0,0,,0),,2) ) Or: =LET( a, HSTACK(Albums3D), b, DROP(a,1), v, TOCOL(b), w, COLUMNS(a), i, SEQUENCE(ROWS(b)), j, QUOTIENT(SEQUENCE(,w,0),w/SHEETS(Albums3D)), f, LAMBDA(x,TOCOL(IF({1},x,b))), DROP(PIVOTBY(HSTACK(f(j),f(i)),f(TAKE(a,1)),v,SINGLE,0,0,,0,,v<>""),,2) ) I'm not really sure if there's any difference in efficiency between the two. I'd like to think the second one is faster because it removes the blank records in one swoop with the [filter_array] argument of PIVOTBY, whereas the first method removes the blank records for every instance of TOCOL (4 times), but I didn't run any tests with larger datasets. Cheers!Re: Wider Excel discussion
Agreed: a built-in EVALTHUNKARRλ function would be ideal. The STYLE concept would also be neat, if they could pull it off. Perhaps something akin to CSS, where you define and apply your own custom styles? Sounds like Pivot Tables are getting an "Auto-Refresh" option, which is long overdue. GROUPBY and PIVOTBY are great, but difficult to format (they also lack basic options like choosing to "insert blank line after each item label" or to NOT "repeat item labels"). I tend to use them more as an intermediary step for solving larger problems, rather than the final output. It would be super helpful if Conditional Formatting rules could be applied to a dynamic spill range. 😏Re: Wider Excel discussion
My biggest pet peeve is that DROP-REDUCE-VSTACK and MAKEARRAY-INDEX are still the most commonly suggested dynamic array methods found on any Excel forum. I too was initially drawn to them when I made the switch to Office 365, as the logic is very similar to a For Each / For Next loop in VBA. Fortunately, David Leal was kind enough to advise me very early on about their efficiency issues, and I've been actively seeking out and sharing alternative methods ever since. It feels like a losing battle, though, as everywhere I turn it's DROP-REDUCE-VSTACK and MAKEARRAY-INDEX over and over again... Not sure if that's what you meant by "low-grade material", but they're definitely "low-grade" in my book. Your custom MAPλ and EVALTHUNKARRλ functions are still the best generic alternatives I've come across to date. ;)Re: Dynamically filter table on basis of range of criteria
Not 100% sure what type of criteria you wanted because "it should be an AND filter so the column should contain any of these words" is somewhat contradictory. "Any of these words" suggests OR criteria, whereas "all of these words" would mean AND criteria. If you have the new REGEXTEST function, you could try the following using OR criteria: =CHOOSECOLS(FILTER(Table3[[SuppliersTradeItemId]:[GrossPrice]],REGEXTEST(Table3[Description],TEXTJOIN("|",,Table4[Criteria]))),1,2,-1) Also, using AND criteria: =CHOOSECOLS(FILTER(Table3[[SuppliersTradeItemId]:[GrossPrice]],BYROW(REGEXTEST(Table3[Description],TOROW(Table4[Criteria],1)),AND)),1,2,-1) There's a good video on YouTube, Excel Filter Function With RegexTest Function, which includes various other examples; however, no explanation was offered for using AND criteria... the formula shown above was the best I could figure it, utilizing BYROW-AND over an array of patterns. If someone else knows of a better way, I'd love to hear it.Re: Summarize multiple columns of varying row lengths into one column.
Another approach, using the TOCOL-IFS method: =LET( rng, TRIMRANGE(A3:K10000,2,0), val, FILTER(DROP(rng,1),TAKE(rng,1)="Analyte"), tst, val<>"", fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2)), key, fnλ(SCAN("",TAKE(DROP(rng,1),,1),LAMBDA(a,v,IF(v="",a,v)))), SORTBY(HSTACK(IF(key=DROP(VSTACK("",key),-1),"",key),fnλ(val)),key,1,fnλ(SEQUENCE(,COLUMNS(val))),1) ) See attached, if needed...Re: Table Merge from Web
When you first select the desired tables to be imported, click the drop-down arrow beside "Load" and select "Load To...", then select "Only Create Connection" and click "OK". If you've already imported these tables into your workbook, go to Data > Queries and Connections, right-click on each table query, select "Load To..." and change it to "Only Create Connection". After all of the desired tables have been imported as "Connection only", go to Data > Get Data > Launch Power Query Editor..., select the first table query in the left-hand pane, then go to Home > Append Queries > Append Queries as New and add the applicable tables to append. From there you can modify the query as needed to clean the data, then "Close and Load" to a table in the workbook. Please see the attached sample file, if needed...Re: Dynamic Calendar in Excel 365
DaeyunPablo Better late than never... I revisited this today with improved methods, which can handle a date range spanning hundreds of years efficiently: =LET( wrap_count, 3, low, MIN(B7:B15), upp, MAX(B7:B15), num, CEILING.MATH((YEAR(upp) - YEAR(low)) * 12 + MONTH(upp) - MONTH(low) + 1, wrap_count), beg, DATE(YEAR(low), MONTH(low) + SEQUENCE(num,, 0), 1), pad, EXPAND("", ROWS(beg), 3, ""), lbl, EXPAND(TEXT(beg, "mmm-e"),, 4, ""), hdr, IFNA(TEXT(SEQUENCE(, 7), "ddd"), beg), bdy, beg - WEEKDAY(beg) + SEQUENCE(, 42), arr, HSTACK(pad, lbl, hdr, (MONTH(bdy) = MONTH(beg)) * bdy), h, 8, w, 7, n, wrap_count * w, i, SEQUENCE(COUNTA(arr) / n,, 0), j, SEQUENCE(, n, 0), INDEX(arr, 1 + QUOTIENT(j, w) + wrap_count * QUOTIENT(i, h), 1 + MOD(j, w) + w * MOD(i, h)) ) Adjust the wrap_count as desired. Cheers!
