Operations Dashboard in Excel

So 3 very capable volunteers have answered, but I think I'm reading your question differently.  It sounds like you have what you want but just want to rearrange those outputs in the second column to line up with the prior column.  This is hard to accurately answer without knowing the formula(s) (assuming those are based on formulas) you are using.   In particular if that is the output of individual formulas (i.e. using traditional non-array formulas), or a sindle array formula for each column, or a more complicated array formula outputing multiple columns.

That said, here is a LAMBDA function that you can use to rearrange a list output to match a prior list where possible and 'fill in' the non-matching items:

=LET(in, B2:.B99, prior, A2:.A99,
AlignLists, LAMBDA(listNew,listOld, LET(
                   InsertItem, LAMBDA(value,list,insertIndex, IF(insertIndex=1,VSTACK(value,DROP(list,1)),
                                                                IF(insertIndex>ROWS(list), VSTACK(EXPAND(list,insertIndex-1,,""),value),
                                                                IF(insertIndex=ROWS(list), VSTACK(DROP(list,-1),value),
                                                                                           VSTACK(TAKE(list,insertIndex-1),value,DROP(list,insertIndex)))))),
                   AlignMatches, LAMBDA(matches,prior,
                                        REDUCE("",in,LAMBDA(p,x, LET(I,XMATCH(x,prior), IF(ISNUMBER(I),InsertItem(x,p,I),p))))),
                   Fillin, LAMBDA(values,list, REDUCE(list, values, LAMBDA(p,q, LET(i, XMATCH("",p), InsertItem(q,p,IF(ISNUMBER(i),i,ROWS(p)+1)))))),
                   MatchedList, AlignMatches(in,prior),
                   nonMatches, UNIQUE(VSTACK(MatchedList,in,"",""),,1),
                   Fillin(nonMatches,MatchedList)
                   )),
AlignLists(in,prior))

and showing it in action:

so that formula has the 'inputs' on line 1 and the output is generated on line 14.  The rest of it from line 2 to line 13 is a single LAMBDA function made up of 3 sub-functions.  So if you want to do this a lot on the sheet then I highly recommend you save that AlignLists LAMBDA function into your NAME MANAGER (under the Formula tab). 

So before I get into lots of details on how to do it or how this works, please just let me know:

a) does this sounds like what you want/need

b) do you need help implementing it? (if so more info on your formulas and sheet structure would help) 

c) do you need/want an explanation on how it works (i.e. breakdown of the steps and sub-functions)

The core issue is that Excel naturally wants to list items in rows, but your dashboard requires a matrix where the same "entity" (the aircraft) spans multiple columns (dates).

To solve the "cell alignment" issue (VH-AA6 appearing in D2 and E2), you cannot rely on standard formulas like XLOOKUP or FILTER alone because they return lists that expand downward, not horizontally across merged cells.

You need to fundamentally change the logic to a "Grid Fill" logic. Here is a VBA solution proposal as an alternative to the other approaches, which automates your entire dashboard with the click of a button.

mathetes, don't worry that you or anyone else might fly with this airline; judging by the aircraft prefix, they are private planes from Down Under 😀.

VBA Code:

Option Explicit

Sub GenerateOperationsDashboard_FixedBays_Refined()
    Dim wsData As Worksheet
    Dim wsDash As Worksheet
    Dim lastRow As Long, i As Long, j As Long, d As Long
    Dim dataArr As Variant
    Dim dictDates As Object
    Dim datesArr As Variant
    Dim colMap As Object
    Dim locationRows As Variant
    Dim numLocations As Integer
    
    ' --- CONFIGURATION ---
    Set wsData = ThisWorkbook.Sheets("RawData")
    Set wsDash = ThisWorkbook.Sheets("Dashboard")
    
    ' --- YOUR ACTUAL LOCATIONS from the image ---
    locationRows = Array("MEL", "Hangar", "Line")
    numLocations = UBound(locationRows) - LBound(locationRows) + 1
    
    ' Speed & Safety
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error GoTo CleanExit
    
    ' 1. PREPARE DASHBOARD
    wsDash.Cells.Clear
    wsDash.Cells.Interior.Color = xlNone
    
    ' 2. LOAD DATA
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No data found in RawData sheet."
        GoTo CleanExit
    End If
    
    ' Assuming columns: A=Tail, B=Start, C=End, D=WorkOrder, E=City, F=Site, G=Hours
    dataArr = wsData.Range("A2:G" & lastRow).Value
    
    ' 3. SORT BY START DATE
    dataArr = SortArrayByColumn(dataArr, 2)
    
    ' 4. EXTRACT & SORT DATES
    Set dictDates = CreateObject("Scripting.Dictionary")
    Set colMap = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(dataArr, 1)
        If IsDate(dataArr(i, 2)) Then dictDates(CDbl(dataArr(i, 2))) = 1
        If IsDate(dataArr(i, 3)) Then dictDates(CDbl(dataArr(i, 3))) = 1
    Next i
    
    datesArr = dictDates.Keys
    BubbleSort datesArr
    
    ' Write Date Headers (Starting from row 1, column 2 as per your image)
    wsDash.Cells(1, 1).Value = "Location"  ' Your first column label
    For i = LBound(datesArr) To UBound(datesArr)
        wsDash.Cells(1, i + 2).Value = CDate(datesArr(i))
        wsDash.Cells(1, i + 2).NumberFormat = "dd/mm"  ' Your format shows dd/mm
        colMap(CDate(datesArr(i))) = i + 2
    Next i
    
    ' Write Location Labels (your MEL, Hangar, Line)
    For i = 0 To numLocations - 1
        wsDash.Cells(i + 2, 1).Value = locationRows(i)
        wsDash.Cells(i + 2, 1).Font.Bold = True
    Next i
    
    ' 5. THE SLOTTING ENGINE
    Dim locationOccupancy() As Boolean
    ReDim locationOccupancy(1 To numLocations, 1 To UBound(datesArr))
    
    Dim tail As String, woNum As String, woStart As Date, woEnd As Date
    Dim city As String, site As String, manhours As Double
    Dim colStart As Long, colEnd As Long
    Dim startIdx As Long, endIdx As Long
    Dim locationFound As Boolean
    Dim locNum As Integer
    Dim rngToMerge As Range
    
    ' Dictionary to accumulate manhours per date
    Dim manhoursDict As Object
    Set manhoursDict = CreateObject("Scripting.Dictionary")
    
    ' Process each work order
    For i = 1 To UBound(dataArr, 1)
        tail = dataArr(i, 1)
        woStart = dataArr(i, 2)
        woEnd = dataArr(i, 3)
        woNum = dataArr(i, 4)
        city = dataArr(i, 5)
        site = dataArr(i, 6)
        manhours = dataArr(i, 7)
        
        ' Get Column Indices
        If Not colMap.Exists(woStart) Or Not colMap.Exists(woEnd) Then GoTo SkipWO
        colStart = colMap(woStart)
        colEnd = colMap(woEnd)
        
        ' Get Date indices
        startIdx = GetDateIndex(woStart, datesArr)
        endIdx = GetDateIndex(woEnd, datesArr)
        
        ' Add to manhours total for each date
        For d = colStart To colEnd
            Dim dateKey As String
            dateKey = CStr(wsDash.Cells(1, d).Value)
            manhoursDict(dateKey) = manhoursDict(dateKey) + manhours
        Next d
        
        ' Determine which location this belongs to (MEL, Hangar, or Line)
        ' You'll need logic based on your data - example:
        locNum = 0
        Select Case UCase(site)
            Case "MEL", "MELBOURNE"
                locNum = 1
            Case "HANGAR", "HEAVY"
                locNum = 2
            Case "LINE", "TRANSIT"
                locNum = 3
            Case Else
                locNum = 2 ' Default to Hangar
        End Select
        
        ' Skip if location not found
        If locNum = 0 Then GoTo SkipWO
        
        ' Check if this location row is free for the duration
        locationFound = False
        Dim isFree As Boolean
        isFree = True
        
        For d = startIdx To endIdx
            If locationOccupancy(locNum, d) Then
                isFree = False
                Exit For
            End If
        Next d
        
        If isFree Then
            ' Mark as occupied
            For d = startIdx To endIdx
                locationOccupancy(locNum, d) = True
            Next d
            
            ' --- DRAW THE BLOCK ---
            With wsDash.Cells(locNum + 1, colStart)  ' +1 because row 1 has dates
                .Value = tail & vbLf & woNum
                
                ' Color Coding based on site/check type
                Select Case UCase(site)
                    Case "HANGAR", "HEAVY"
                        .Interior.Color = RGB(198, 239, 206) ' Green
                    Case "LINE", "TRANSIT"
                        .Interior.Color = RGB(255, 235, 156) ' Yellow
                    Case Else
                        .Interior.Color = RGB(220, 220, 220) ' Grey
                End Select
                
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .Font.Size = 9
            End With
            
            ' Merge if multi-day
            If colEnd > colStart Then
                Set rngToMerge = wsDash.Range(wsDash.Cells(locNum + 1, colStart), _
                                              wsDash.Cells(locNum + 1, colEnd))
                rngToMerge.Merge
                rngToMerge.Borders.Weight = xlThin
            Else
                wsDash.Cells(locNum + 1, colStart).Borders.Weight = xlThin
            End If
        Else
            ' Location row already occupied - log to debug sheet
            Debug.Print "Conflict: " & tail & " at " & locNum & " on dates " & startIdx & "-" & endIdx
        End If
        
SkipWO:
    Next i
    
    ' --- ADD TOTAL MANHOURS ROW (as seen in your image) ---
    Dim totalRow As Integer
    totalRow = numLocations + 2  ' After MEL, Hangar, Line
    
    wsDash.Cells(totalRow, 1).Value = "Total Manhours"
    wsDash.Cells(totalRow, 1).Font.Bold = True
    
    ' Fill in manhours for each date
    For i = LBound(datesArr) To UBound(datesArr)
        Dim currentDate As Date
        currentDate = CDate(datesArr(i))
        dateKey = CStr(currentDate)
        
        If manhoursDict.Exists(dateKey) Then
            wsDash.Cells(totalRow, i + 2).Value = manhoursDict(dateKey)
        Else
            wsDash.Cells(totalRow, i + 2).Value = 0
        End If
        wsDash.Cells(totalRow, i + 2).HorizontalAlignment = xlCenter
    Next i
    
    ' Format the total row
    wsDash.Rows(totalRow).Font.Bold = True
    wsDash.Rows(totalRow).Interior.Color = RGB(240, 240, 240)
    
    ' Auto-fit columns
    wsDash.Columns.AutoFit
    
    MsgBox "Dashboard Generated with " & numLocations & " locations!"

CleanExit:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

' Helper function to get date index
Function GetDateIndex(ByVal targetDate As Date, ByRef datesArr As Variant) As Long
    Dim i As Long
    For i = LBound(datesArr) To UBound(datesArr)
        If Abs(CDate(datesArr(i)) - targetDate) < 0.1 Then
            GetDateIndex = i + 1  ' 1-based for occupancy array
            Exit Function
        End If
    Next i
    GetDateIndex = 0
End Function

' Sort array by column (your existing function)
Function SortArrayByColumn(arr As Variant, colIndex As Integer) As Variant
    ' ... (keep your existing implementation) ...
End Function

Private Sub BubbleSort(ByRef arr As Variant)
    ' ... (keep your existing implementation) ...
End Sub

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

HelloSiddhi817​,

The alignment issue occurs because each date column is evaluated independently, so Excel places aircraft in the next available row for that specific day. This causes aircraft that span multiple days to appear on different rows.

To keep the aircraft aligned, the formula must check whether the column date falls between the aircraft StartDate and EndDate.

If your ERP data is stored in a table with columns Tail, StartDate, EndDate and ManHours, place the schedule dates across row 1 and list the aircraft tails in column A.

In the schedule grid use a formula such as

=IF(SUMPRODUCT((Table1[Tail]=$A2)(D$1>=Table1[StartDate])(D$1<=Table1[EndDate])),$A2,"")

A2 contains the aircraft tail and D1 contains the column date.

If the column date falls between the StartDate and EndDate the aircraft tail is returned. Because the aircraft remains tied to the same row, it automatically appears in the same row across consecutive days such as VH-AA6 appearing on both 8 Mar and 9 Mar.

Daily manhours can be calculated with

=SUMPRODUCT((D1>=Table1[StartDate])*(D1<=Table1[EndDate])*Table1[ManHours])

You can also add a search cell and apply conditional formatting with

=ISNUMBER(SEARCH($B$1,D2))

Typing a tail number will highlight all matching cells in the schedule.

Microsoft documentation

https://support.microsoft.com/en-gb/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e

I'd like to ask what airline you're working for so that I can avoid ever booking a flight with that airline. I say that partly in jest, but also to make a point: it worries me that you're trying to manage something so critical by designing a spreadsheet to track it....surely there already exists software to manage this workflow and all other associated matters.

If this is a serious request, you should be going to a consultant who works professionally on this type of thing and who is well compensated for that work (as contrasted with volunteers here who just enjoy helping people get over some of the hurdles of learning Excel). Definitely you shouldn't be using ChatGPT, not for something this critical.