Forum Widgets
Latest Discussions
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
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.
Tokyo Stock Exchange data
Does any one know, and can share, a sample excel file that can import updated information (ticker, name, last price, dividend yield, daily high and daily low and analysts average target price) for ~50 stocks traded on the Tokyo stock exchange, which is not covered by Microsoft?
Cleaning messy excel/csv form ERP system
Hi, I’m curious how people here usually deal with messy exports coming from ERP or accounting systems. I often receive Excel or CSV files that are not really ready to use as a table. Things like: -a few rows of system info before the header -totals or notes mixed inside the data -different date formats in the same column -currency symbols and formats all over the place -sometimes even another small table sitting under the first one Before doing any analysis (excel, power BI...) I usually have to spend a bit of time cleaning the file so it becomes a normal structured table. Power query definitely helps, but depending on the export I sometimes still end up doing several cleanup steps first. I’m curious what the usual workflow is for people here. Do you normally build a reusable Power Query transformation for these reports, or do you handle each export differently? I recently walked through one messy export example here while documenting the cleanup steps if anyone is curious about the kind of file I mean: https://www.mubsiraanalytics.com/blog/how-to-extract-clean-tables-from-messy-excel-csv Mostly just interested to see how others approach this."Method 'Calculation' of object '_Application' failed" error occurs on unpredictable attempts
At random times, this save code decides to spit the "Method 'Calculation' of object '_Application' failed" error. It doesn't happen on any other userforms. Any idea why? Private Sub CommandButton2_Click() 'Save 'Initial: On Error Resume Next Dim rng As Range, cell As Range Dim first_DB_avail_row As Range Dim Highest_Version_Row As Long Dim existingVersions() As String Dim ver_find As Variant Dim ver_list As Object: Set ver_list = CreateObject("System.Collections.ArrayList") 'Use an ArrayList for version sorting Dim padded_list As Object: Set padded_list = CreateObject("System.Collections.ArrayList") 'Create a temporary list for padded versions to ensure order (e.g., 5.1.28 > 5.1.2) Dim v As Variant, parts As Variant Dim padded_v As String, leadChar As String, all_vers As String Dim i As Integer 'Validate entries: If Me.Caption = "First Version - Business Manager" Then 'Adding product - first version If Me.ComboBox1.Value = "" Or Me.TextBox1.Value = "" Or _ Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then 'Check if version is not inputted MsgBox "You must complete all fields.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Insert_Product.ver_val = stage_entry & Major & Minor & Patch Unload Me Insert_Product.new_product_ver_cancel = False 'Set back to false from the terminate sub setting GoTo MEM_CLEAN End If Call Find_Latest_Ver 'Get the current latest version If stage_entry & Major & Minor & Patch = Highest_Version Then 'Check if version already exists MsgBox "This version already exists (as the newest version).", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If existingVersions = Split(Replace(Me.TextBox4.Value, vbCrLf, ""), "• ") For Each ver_find In existingVersions If Trim(ver_find) = Trim(stage_entry & Major & Minor & Patch) Then MsgBox "This version already exists.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Next ver_find If Me.ComboBox1.Value = "" Or Me.TextBox1.Value = "" Or _ Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then 'Check if version is not inputted MsgBox "You must complete all fields.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Me.Hide 'This will preserve public variables, keeping the form loaded, while still allowing the PLZ_WAIT userForm to display (no modal error) 'Macro Enhancement - Start: Application.Calculation = xlCalculationManual ActiveWorkbook.UpdateRemoteReferences = False Application.EnableEvents = False 'This must be false Application.ScreenUpdating = False Application.Interactive = False Application.DisplayAlerts = False Application.DisplayStatusBar = False PLZ_WAIT.Show PLZ_WAIT.Label2.Caption = "Setting new version" DoEvents 'Allows the PLZ_WAIT userForm to display If Err.Number <> 0 Then 'For some unknown reason, the Excel error "Method 'Calculation' of object '_Application' failed" occurs on unpredictable/unrepeatable attempts to save (sub runs) - cause unknown MsgBox "An Excel error occured (""" & Err.Description & """: " & Err.Number & "). Please try again (until it works).", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If 'Pull data from the latest version: ThisWorkbook.Sheets("Products").Unprotect Password:=ThisWorkbook.Sheets("Background Data").Range("CY39").Value For Each cell In ThisWorkbook.Sheets("Background Data").Range("E4:E7503") If cell.Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value Then If cell.Offset(0, -2).Value = Highest_Version Then ThisWorkbook.Sheets("Products").Range("B" & Selection.Row).Value = cell.Offset(0, -3).Value 'Name ThisWorkbook.Sheets("Products").Range("C" & Selection.Row).Value = stage_entry & Major & Minor & Patch 'Product Version ThisWorkbook.Sheets("Products").Range("D" & Selection.Row).Value = cell.Offset(0, -1).Value 'File ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value = cell.Value 'ID Number ThisWorkbook.Sheets("Products").Range("F" & Selection.Row).Value = cell.Offset(0, 1).Value 'Category ThisWorkbook.Sheets("Products").Range("G" & Selection.Row).Value = cell.Offset(0, 2).Value 'Details (Description) ThisWorkbook.Sheets("Products").Range("K" & Selection.Row).Value = cell.Offset(0, 6).Value 'Release Date ThisWorkbook.Sheets("Products").Range("L" & Selection.Row).Value = cell.Offset(0, 7).Value 'Copyright Y/N button ThisWorkbook.Sheets("Products").Range("M" & Selection.Row).Value = cell.Offset(0, 8).Value 'Copyright Status ThisWorkbook.Sheets("Products").Range("N" & Selection.Row).Value = cell.Offset(0, 9).Value 'Year ThisWorkbook.Sheets("Products").Range("O" & Selection.Row).Value = cell.Offset(0, 10).Value 'Copyright Statement ThisWorkbook.Sheets("Products").Range("P" & Selection.Row).Value = cell.Offset(0, 11).Value 'Published Y/N button ThisWorkbook.Sheets("Products").Range("Q" & Selection.Row).Value = cell.Offset(0, 12).Value 'Publish Status (Date) ThisWorkbook.Sheets("Products").Range("R" & Selection.Row).Value = cell.Offset(0, 13).Value 'Web Link ThisWorkbook.Sheets("Products").Range("S" & Selection.Row).Value = cell.Offset(0, 14).Value 'Withdraw Date Highest_Version_Row = cell.Row Exit For End If End If Next cell 'Save new version to version database: Set first_DB_avail_row = ThisWorkbook.Sheets("Background Data").Range(ThisWorkbook.Sheets("Background Data").Range("C7506").End(xlUp).Offset(1, 0).Address) first_DB_avail_row.Offset(0, -1).Value = ThisWorkbook.Sheets("Products").Range("B" & Selection.Row).Value 'Name first_DB_avail_row.Value = ThisWorkbook.Sheets("Products").Range("C" & Selection.Row).Value 'Product Version first_DB_avail_row.Offset(0, 1).Value = ThisWorkbook.Sheets("Products").Range("D" & Selection.Row).Value 'File first_DB_avail_row.Offset(0, 2).Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value 'ID Number first_DB_avail_row.Offset(0, 3).Value = ThisWorkbook.Sheets("Products").Range("F" & Selection.Row).Value 'Category first_DB_avail_row.Offset(0, 4).Value = ThisWorkbook.Sheets("Products").Range("G" & Selection.Row).Value 'Details (Description) first_DB_avail_row.Offset(0, 8).Value = ThisWorkbook.Sheets("Products").Range("K" & Selection.Row).Value 'Release Date first_DB_avail_row.Offset(0, 9).Value = ThisWorkbook.Sheets("Products").Range("L" & Selection.Row).Value 'Copyright Y/N button first_DB_avail_row.Offset(0, 10).Value = ThisWorkbook.Sheets("Products").Range("M" & Selection.Row).Value 'Copyright Status first_DB_avail_row.Offset(0, 11).Value = ThisWorkbook.Sheets("Products").Range("N" & Selection.Row).Value 'Year first_DB_avail_row.Offset(0, 12).Value = ThisWorkbook.Sheets("Products").Range("O" & Selection.Row).Value 'Copyright Statement first_DB_avail_row.Offset(0, 13).Value = ThisWorkbook.Sheets("Products").Range("P" & Selection.Row).Value 'Published Y/N button first_DB_avail_row.Offset(0, 14).Value = ThisWorkbook.Sheets("Products").Range("Q" & Selection.Row).Value 'Publish Status (Date) first_DB_avail_row.Offset(0, 15).Value = ThisWorkbook.Sheets("Products").Range("R" & Selection.Row).Value 'Web Link first_DB_avail_row.Offset(0, 16).Value = ThisWorkbook.Sheets("Products").Range("S" & Selection.Row).Value 'Withdraw Date 'Save Development Status Data to new version from latest version (copy over): first_DB_avail_row.Offset(0, 17).Value = ThisWorkbook.Sheets("Background Data").Range("T" & Highest_Version_Row).Value 'Title first_DB_avail_row.Offset(0, 18).Value = ThisWorkbook.Sheets("Background Data").Range("U" & Highest_Version_Row).Value 'Tags first_DB_avail_row.Offset(0, 19).Value = ThisWorkbook.Sheets("Background Data").Range("V" & Highest_Version_Row).Value 'Content first_DB_avail_row.Offset(0, 20).Value = ThisWorkbook.Sheets("Background Data").Range("W" & Highest_Version_Row).Value 'Total Tasks first_DB_avail_row.Offset(0, 21).Value = ThisWorkbook.Sheets("Background Data").Range("X" & Highest_Version_Row).Value 'Complete Tasks first_DB_avail_row.Offset(0, 22).Value = ThisWorkbook.Sheets("Background Data").Range("Y" & Highest_Version_Row).Value 'Platform first_DB_avail_row.Offset(0, 23).Value = ThisWorkbook.Sheets("Background Data").Range("Z" & Highest_Version_Row).Value 'Medium first_DB_avail_row.Offset(0, 24).Value = ThisWorkbook.Sheets("Background Data").Range("AA" & Highest_Version_Row).Value 'Framework first_DB_avail_row.Offset(0, 25).Value = ThisWorkbook.Sheets("Background Data").Range("AB" & Highest_Version_Row).Value 'Stage first_DB_avail_row.Offset(0, 26).Value = ThisWorkbook.Sheets("Background Data").Range("AC" & Highest_Version_Row).Value 'Dev Log (1) first_DB_avail_row.Offset(0, 102).Value = ThisWorkbook.Sheets("Background Data").Range("DA" & Highest_Version_Row).Value 'Dev Log (2) first_DB_avail_row.Offset(0, 103).Value = ThisWorkbook.Sheets("Background Data").Range("DB" & Highest_Version_Row).Value 'Dev Log (3) first_DB_avail_row.Offset(0, 104).Value = ThisWorkbook.Sheets("Background Data").Range("DC" & Highest_Version_Row).Value 'Dev Log (4) first_DB_avail_row.Offset(0, 105).Value = ThisWorkbook.Sheets("Background Data").Range("DD" & Highest_Version_Row).Value 'Dev Log (5) first_DB_avail_row.Offset(0, 106).Value = ThisWorkbook.Sheets("Background Data").Range("DE" & Highest_Version_Row).Value 'Dev Log (6) first_DB_avail_row.Offset(0, 107).Value = ThisWorkbook.Sheets("Background Data").Range("DF" & Highest_Version_Row).Value 'Dev Log (7) first_DB_avail_row.Offset(0, 27).Value = ThisWorkbook.Sheets("Background Data").Range("AD" & Highest_Version_Row).Value 'Total Bugs first_DB_avail_row.Offset(0, 28).Value = ThisWorkbook.Sheets("Background Data").Range("AE" & Highest_Version_Row).Value 'Resolved Bugs first_DB_avail_row.Offset(0, 29).Value = ThisWorkbook.Sheets("Background Data").Range("AF" & Highest_Version_Row).Value 'Total Requests first_DB_avail_row.Offset(0, 30).Value = ThisWorkbook.Sheets("Background Data").Range("AG" & Highest_Version_Row).Value 'Complete Requests first_DB_avail_row.Offset(0, 31).Value = ThisWorkbook.Sheets("Background Data").Range("AH" & Highest_Version_Row).Value 'Start Date first_DB_avail_row.Offset(0, 32).Value = ThisWorkbook.Sheets("Background Data").Range("AI" & Highest_Version_Row).Value 'End Date first_DB_avail_row.Offset(0, 33).Value = ThisWorkbook.Sheets("Background Data").Range("AJ" & Highest_Version_Row).Value 'Total Work Days first_DB_avail_row.Offset(0, 34).Value = ThisWorkbook.Sheets("Background Data").Range("AK" & Highest_Version_Row).Value 'Lines of Code first_DB_avail_row.Offset(0, 35).Value = ThisWorkbook.Sheets("Background Data").Range("AL" & Highest_Version_Row).Value 'Number of Features/Amenities first_DB_avail_row.Offset(0, 36).Value = ThisWorkbook.Sheets("Background Data").Range("AM" & Highest_Version_Row).Value 'Ease of Use first_DB_avail_row.Offset(0, 37).Value = ThisWorkbook.Sheets("Background Data").Range("AN" & Highest_Version_Row).Value 'Innovation/Uniqueness first_DB_avail_row.Offset(0, 38).Value = ThisWorkbook.Sheets("Background Data").Range("AO" & Highest_Version_Row).Value 'Complexity first_DB_avail_row.Offset(0, 39).Value = ThisWorkbook.Sheets("Background Data").Range("AP" & Highest_Version_Row).Value 'Optimization first_DB_avail_row.Offset(0, 40).Value = ThisWorkbook.Sheets("Background Data").Range("AQ" & Highest_Version_Row).Value 'Customer Request/Cater 'Set version list: Set rng = ThisWorkbook.Sheets("Background Data").Range("E4:E7503") ver_list.Add stage_entry & Major & Minor & Patch 'Add initial version For Each cell In rng 'Loop to add matches - Collect all versions If cell.Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value Then ver_list.Add cell.Offset(0, -2).Value End If Next cell 'Temporarily convert each version into sortable key (000.000.000) For i = 0 To ver_list.Count - 1 v = ver_list(i) leadChar = Left(v, 1) parts = Split(Mid(v, 2), ".") padded_v = leadChar padded_v = padded_v & Right("000" & parts(0), 3) padded_v = padded_v & Right("000" & parts(1), 3) padded_v = padded_v & Right("000" & parts(2), 3) ver_list(i) = padded_v & "|" & v 'Store padded key + original version 'Note: This converts, for example, "V54.17.44" to "V054017044" in order to sort, for each version (i) Next i 'Sort (descending) then strip padded key: ver_list.Sort: ver_list.Reverse For i = 0 To ver_list.Count - 1 ver_list(i) = Split(ver_list(i), "|")(1) Next i 'Note: This sorts then reverses the sort for highest version to be on top. Since sorting is left-to-right, major number will sort first, then minor, _ then patch, in that order. For the release, order will be A then B then V, since that's the alphabet's order, then it's reversed causing the order to be V then B then A. _ Basically, it is sorted lexicographically (V > B > A) then numerically (000000000), then reversed for descending order, then converted back to versioning format. 'Set validation: all_vers = " ," & Join(ver_list.ToArray, ",") 'Join all in array into one string and add initial blank option (for adding new when selected), for setting validation With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=all_vers .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = False End With ThisWorkbook.Sheets("Products").Protect Password:=ThisWorkbook.Sheets("Background Data").Range("CY39").Value 'Macro Enhancement - End: Application.Calculation = xlCalculationAutomatic ActiveWorkbook.UpdateRemoteReferences = True Application.EnableEvents = True Application.ScreenUpdating = True Application.Interactive = True Application.DisplayAlerts = True Application.DisplayStatusBar = True 'Final: Unload Me 'This clears "Highest_Version" and all public variables ?....... Sheet2.UPDATE_DB_FORCE = True Application.Run "Sheet2.Worksheet_Change", Selection 'Necessary in order to update Pricing and CUS_PRO_RATINGS sheets with new version Sheet2.UPDATE_DB_FORCE = False 'Release variables/objects from memory: MEM_CLEAN: Unload PLZ_WAIT: Set PLZ_WAIT = Nothing Set rng = Nothing Set cell = Nothing Set first_DB_avail_row = Nothing ver_list.Clear: Set ver_list = Nothing Set padded_list = Nothing End Sub
