Excel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data array
Cumulative Sum of Each Column or Row
After studying various posts (answers) by members of this community, I developed a function that returns the cumulative sum of each column or row of an array: =LAMBDA(a,[by_row],LET( f,IF(by_row, LAMBDA(b,IF(ROWS(b)=1,b, LAMBDA(b-VSTACK(0,DROP(TAKE(b,,-1),-1)))())), LAMBDA(b,IF(COLUMNS(b)=1,b, LAMBDA(b-HSTACK(0,DROP(TAKE(b,-1),,-1)))()))), IF(by_row, LAMBDA(f(SCAN(0,a,SUM)))(), LAMBDA(f(TRANSPOSE(SCAN(0,TRANSPOSE(a),SUM))))()))) My goal is maximum efficiency. I am new to the concept of lazy evaluation, so I'm wondering if you could explain the flow in detail and whether there is a pair of sets of parentheses too much. Of course, I'm open to improvements.Date entered is changed to a different date
This issue has just started as far as I can tell. On an existing worksheet with the column formated to "date" 01/01/2025 format - when I enter a date of 01/01/2025 as for an example and hit "enter", the date is changed to 10/29/70! If I enter a date of 01/19/2026 it is changed to 08/27/63! and so on,..... it changes the field every time. if I enter those same dates by physically entering 01 slash 19 slash 2026 slash it will stay as I typed it - and will look like 01/19/2026 (or 01 slash 01 slash 2025) My husband is able to create a spreadsheet on his laptop that works correctly. When he sent it to me, the date fields did what I have stated above. So there must be something corrupt on my laptop? or Excel? I am running on the latest build (to my knowledge) Anyone? I am at a loss.. When entering a lot of data and dates - this is a real PIA. Plus - it was working just fiine - not sure what has changed. Thank you
Cannot add a worksheet to my Excel spreadsheet
I haver all of a sudden lost the ability to add worksheets to my spreadsheet program. I am using macros that both protect and unprotect the sheets that are currently there, but I want to add another sheet, and the plus sign at the bottom is grayed out as well as the 'Insert Line' line when you do Home/Insert. I have looked and I cannot find the setting that controls the addition of worksheets. Any suggestions???
How can a remove these files from the 'Recent' list?
How can a remove these files from the 'Recent' list please? I've deleted these workbooks from OneDrive, but they still appear in the Recent list. How can I stop them appearing every time I open Excel please? I've tried uninstalling Office and reinstalling. I'm using Office 365. Thank you!
