Clash analysis for manufacturing hall

Here the concept would like to share:

1. Grid Setup:
• Use a worksheet to represent each bay as a grid (e.g., Bay1, Bay2, Bay3)
• Each cell represents a location in the bay
2. Plate Movement Table:
• A table with columns: Plate ID, Move Date, Bay, X, Y
• This is your input data
3. VBA Logic:
• A macro that reads the table and places each plate in the correct cell
• Use cell coloring or shapes to represent plates
• Check for duplicate coordinates on the same date to flag clashes

Sample:

Sub UpdateBayLayout()
    Dim wsInput As Worksheet, wsBay As Worksheet
    Dim lastRow As Long, i As Long
    Dim plateID As String, moveDate As Date, bayName As String
    Dim x As Integer, y As Integer
    Dim cellKey As String
    Dim clashDict As Object
    Set clashDict = CreateObject("Scripting.Dictionary")

    Set wsInput = Sheets("Input")
    lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        plateID = wsInput.Cells(i, 1).Value
        moveDate = wsInput.Cells(i, 2).Value
        bayName = wsInput.Cells(i, 3).Value
        x = wsInput.Cells(i, 4).Value
        y = wsInput.Cells(i, 5).Value

        Set wsBay = Sheets(bayName)
        cellKey = bayName & "_" & x & "_" & y & "_" & moveDate

        If clashDict.exists(cellKey) Then
            wsBay.Cells(y, x).Interior.Color = RGB(255, 0, 0) ' Red for clash
        Else
            clashDict.Add cellKey, plateID
            wsBay.Cells(y, x).Interior.Color = RGB(0, 255, 0) ' Green for OK
        End If
    Next i
End Sub

Clash Detection Logic

• Use a dictionary or array to track occupied cells by date
• If a cell is already occupied on that date, flag it as a clash
• You can even pop up a message box or log clashes in a separate sheet