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
Current Date and time per cell
I have a table with one column listing rows with a dynamic text box (open; closed) in the next column I would like to display the date and time when the status changes. The purpose is to show when a task was completed, I have used the now() function but it keeps refreshing, I need it stay the date The snip of the sheet below shows how is is currently working, so can any one help with some code for column J please. thank you Lee
Change in return of AVERAGE function - Mac Excel
I was puzzled that a spreadsheet I use daily generated an error today that had not existed in earlier versions. Attempts to confirm the validity of the spreadsheet functions by running prior versions that had previously run error free resulted in the same error. Eventually, it seems that the operation of the AVERAGE function has been changed in an Excel program update that was installed yesterday. Previously, if an AVERAGE function addressed a range of empty cells, it would return a zero value. Now it is returning the error #DIV/0! This is strange because the AVERAGE function will now return zero if the addressed range contains zeros rather than just being empty. Not sure if Microsoft intended this change or if the change might also apply to other functions. In muy case, I'm able to change my spreadsheet to provide for this but it would have been nice to have some warning.Multiple Inventory entries on the same item
Okay so I'm wondering if there is a way for excel to automatically advance to the next column when entering inventory data so if I need to make another entry on the same item I don't have to advance to the next cell myself. This would be helpful in streamlining inventory and making it foolproof when adding inventory to a specific item (without having to worry about deleting previously entered data). I'm hoping there is a way to do this so I can take a tablet when completing my inventory and avoid the old clipboard and paper.
Bank recon matching
Happy New Year everyone! Can someone please assist me with this to a matching exercise for a bank reconciliation. Tried to do XLOOKUP with REGEXTEST but no luck. Below is the link to the excel file. https://1drv.ms/x/c/80b0e5c8d40a83ef/IQAn2IxiOBDpSJBIIZCfPj3TAexy2EEPYT5VjpDkcpTzpB4?e=S4NEuo
Duplicate Values that are not exact match
Hello, I have an excel file with names I need to clean up. I started with using conditional formatting to highlight all duplicate values and deleted the duplicates. There are still duplicates in the file that don't share the exact name. For example, there's one cell called "Blackline Safety", and another called "Blackline Safety Corp". These are duplicates, but I don't know how to do it without looking manually. Is there any way to get excel to find the duplicates like that? ThanksSummary sheet, copy entire row if a cell criteria is met
https://arengio-my.sharepoint.com/:x:/g/personal/melanie_stokes_areng_io/IQC2OhPfZnKEQL6qfPj11gqfAQSh8Em3Zp95fkYbLBTZc3g?e=oNU1pEHi Can you please help me, I have a schedule for jobcards, each tab is a book that is given to someone, if that specific jobcard is invoices, then in column T where it says "Incomplete", I want that entire row to be copied to the "Outstanding Jobcards"tab. As a new book is handed out, a new tab is create and the tab is named according to the book sequence. This is updated daily as jobcards are being invoiced.
