unpivot data and handle merged cells without using Power Query (Unpivot_Toolkit)
Hey, guys!! I’ve been working on a set of functional Excel Lambdas to solve a common headache: transforming "Wide" human-readable data into "Long" database formats without having to open Power Query every time. =UNPIVOT_PLUS(table, [no_of_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks],[pad_blanks_with],[pad_errors_with]) Don’t worry about the full list, most use cases only require 5 arguments. I've included a table of default values at the end. Merged Cell Support: Automatically handles fill-down/fill-right logic for merged headers/columns. Bonus Helper: SPLIT_INJECT =SPLIT_INJECT(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with]) It targets specific columns, splits them by a delimiter (like TEXTSPLIT), and expands the entire table horizontally while keeping all other columns perfectly stable. Optional arguments match TEXTSPLIT defaults. Feel free to tear this apart or adapt it for your own edge cases. I’d love to hear how you end up using it! You can grab both functions from my GitHub Gist https://gist.github.com/Medohh2120/f8553c149684e39bb499249e39f01017. Argument Description Default Behavior table The array or range of data to unpivot. Required argument (no default) [no_of_cols] Fixed left columns to keep as identifiers. 1 [no_of_headers] Top rows used as headers, handling merged cells. 1 [attribute_names] Header name for the unpivoted attributes . "Attribute" [value_name] Header name for the unpivoted values. "Value" [remove_errors] Excludes grid rows with formula errors. FALSE [remove_blanks] Removes grid empty cells and empty strings. TRUE [pad_blanks_with] Value to substitute for empty cells. Leaves cell blank [pad_errors_with] Value to substitute for errors. Leaves error as-is
Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
The goal: Ulam spiral - Wikipedia The trick is creating a function capable of producing the largest matrix of numbers possible (this may rule out the recursive approach). The approach I took: The approach I took was creating 'frames' and working inward with REDUCE essentially performing repeated addition: REDUCE works its way through an array of odd numbers in descending order (1 is omitted) with 3 different situations: 1. The odd number is the largest so the matrix does not require padding. 2. The odd number is 3, the anomaly, so a 'padded core' is created. 3. The odd number is not the greatest nor 3 so a padded matrix is generated Spiral Lambda: =LET( s, ODD(INT(SQRT(n))), arr, UNIQUE(ODD(SEQUENCE(s - 1, , s, -1))), arrCore, {5, 4, 3; 6, 1, 2; 7, 8, 9}, IFERROR( REDUCE( 0, arr, LAMBDA(a, v, LET( TopBottomPadding, EXPAND(0, (s - v) / 2, s, 0), SidePadding, EXPAND(0, v, (s - v) / 2, 0), top, SEQUENCE(, v, (v - 1) ^ 2 + 1, -1), bottom, SEQUENCE(, v, (v - 1) ^ 2 + v, 1), left_frame, EXPAND(SEQUENCE(v - 2, , (v - 1) ^ 2 + 2), , v - 1, 0), right_frame, SEQUENCE(v - 2, , (v - 1) ^ 2 - (v - 1), -1), core_stuffing, EXPAND(0, v, (s - v) / 2, 0), core, VSTACK( TopBottomPadding, HSTACK(core_stuffing, arrCore, core_stuffing), TopBottomPadding ), center, HSTACK(left_frame, right_frame), nopad, VSTACK(top, center, bottom), pad, VSTACK( TopBottomPadding, HSTACK(SidePadding, VSTACK(top, center, bottom), SidePadding), TopBottomPadding ), a + IF(v = s, nopad, IF(v = 3, core, pad)) ) ) ), "Please enter a number 17 or greater" ) ) The accuracy checks The highest number I've been able to get away with for n is 300,001. I'm interesting in any suggested improvements or different approaches to this task!Operations Dashboard in Excel
Ok, so I have been tasked to build an operational dashboard for an airline maintenance planning and tracking. I have a dataset downloaded from our ERP system that lists down the aircraft tails, the workorder number, tasks in each workorder, manhours for each task, city, site(hangar or line) and the start date and end dates. There are codes that are assigned for each category of workorder, whether it is a C-Check, Line or transit. In the current scenario, we use a flight operations tracking software that gives us a hangar forecast, but then we have to get the dataset (as mentioned above) and then build a report daily to show the tails assigned for each port and then the total manhours. The report looks something similar to what you see below. Now, instead of doing it daily manually, I want to automate the process. So far, I have been able to sum the total manhours for the day, get the tails assigned for each port and location, and achieve some sort of conditional formatting to distinguish between different types of checks - green for heavy, yellow for transit and so on. What I have been unable to achieve is the aircraft is scheduled for two days grounding in the hangar, then the cells on both days should align together. As you can see in the image above, VH-AA6 has maintenance on the 8th and 9th of March, but the cells are not aligned. I tried to find a difference between the start and end date and create a helper column to assign a priority, but it didn't work. I have spent countless hours on Chatgpt to come up with a solution, but all efforts went in vague. I have seen a similar excel sheet elsewhere, but I couldn't extract the formulas or the logic since it was heavily protected. In the end I want to add a search bar and a to find a Rego/ Tail by typing in the search field and highlight in the sheet quickly. The main aim is the cell alignment for the consecutive dates. So lets say AA6 is occupying D2 on Day 8, then on the Day9 AA6 should pop up in E2. Any other aircraft on the Day9 with a day's grounding may appear in E1, or next available empty cell.
change background color
I have a worksheet, using column b9:f550 with the cells having a background color of blue. Col B is empty of data. whenever I put a S or P in column B, ( b9 example, I want the background color to change in b9, c9, d9, e9, f9 to white. is there a way that this will automatically change to white whenever I add a S or P in the B col?
Filter Function or TAKE-DROP Function
Dear Experts, I have a Data like this:- Column A -> Has the File Names, and Column B,C,D have their corresponding data, In Column F I have the unique File Names and from G/H/I -> I need the start of the hfn/sfn/slot and in J/K/L the end of the hfn/sfn/slot for that File Name as populated , How to achieve this? Thanks & Regards Anupam Shrivastava
Want to add a minus number, alongside the existing formula
Hi all, I have here a handicap calculator that's primarily used for tenpin bowling and I would like to add an enable / disable (with Yes or No) a Minus Handicap along with the existing formula for each cell (in columns B, E, H & K), plus the option to adjust the percentage to affect only the minus numbers. Hope it all makes sense. Thanks in advance.
Error al modificar una antigua formula
Estoy intentando modificar una formula que tenia antes unicamente cambiando lo que es la cantidad por la que se sumaba a la que tenia la anterior, lo demas de la formula esta completamente igual, pero por alguna razon me salta esa pestaña diciendo que hay algun problema con la formula, estuve buscando si hay algun tipo de error pero no lo encontre, inclusive llegue a copiar la primera formula original y no funciona aun asi, por mas que lo intento siempre me salta la misma advertencia y no me deja agregar la formula como es debido. cabe decir que la formula es la que esta abajo de la pestaña de advertencia y la celda "B10" es la de la cantidad de "300,000" que esta encima de los "354,000"
