Dear Team,
I would like to inform you that the issue has been successfully resolved using a VBA code solution. Thank you for your support and cooperation.
Sub PopulateFaultCodesAndSumYield()
Dim wsDelay As Worksheet, wsCutting As Worksheet, wsOutput As Worksheet
Dim lastRowDelay As Long, lastRowCutting As Long, lastRowOutput As Long, lastColOutput As Long
Dim i As Long, j As Long, outputRowNum As Long
Dim startTime As Double, endTime As Double, slotStart As Double, slotEnd As Double
Dim workCenter As String, faultCode As String, department As String, bayNo As String
Dim downTimeDate As Variant, postingDate As Variant, existingData As String, timeSlot As String
Dim combinedValue As String, uniqueFaultCodes As Object
Dim yieldSum As Double, yieldDict As Object
' Set worksheets
Set wsDelay = ThisWorkbook.Sheets("Delay")
Set wsCutting = ThisWorkbook.Sheets("Cutting")
Set wsOutput = ThisWorkbook.Sheets("Cutting Summary")
' Find last row & column dynamically
lastRowDelay = wsDelay.Cells(wsDelay.Rows.Count, 1).End(xlUp).Row
lastRowCutting = wsCutting.Cells(wsCutting.Rows.Count, 1).End(xlUp).Row
lastRowOutput = wsOutput.Cells(wsOutput.Rows.Count, 1).End(xlUp).Row
lastColOutput = wsOutput.Cells(2, wsOutput.Columns.Count).End(xlToLeft).Column
' Clear previous fault codes & yield data in Summary (Only in Time Slot Columns)
wsOutput.Range(wsOutput.Cells(3, 5), wsOutput.Cells(lastRowOutput, lastColOutput)).ClearContents
' Initialize dictionaries for unique fault codes and yield summation
Set uniqueFaultCodes = CreateObject("Scripting.Dictionary")
Set yieldDict = CreateObject("Scripting.Dictionary")
' Loop through Delay sheet (Fault Codes)
For i = 2 To lastRowDelay
If IsNumeric(wsDelay.Cells(i, 4).Value) Then startTime = CDbl(wsDelay.Cells(i, 4).Value) Else GoTo SkipFaultRow
If IsNumeric(wsDelay.Cells(i, 6).Value) Then endTime = CDbl(wsDelay.Cells(i, 6).Value) Else GoTo SkipFaultRow
workCenter = Trim(wsDelay.Cells(i, 8).Value)
faultCode = Trim(wsDelay.Cells(i, 15).Value)
downTimeDate = wsDelay.Cells(i, 21).Value
If Not IsDate(downTimeDate) Then GoTo SkipFaultRow
downTimeDate = CDate(downTimeDate)
' Loop through all rows in Summary to find a match
For outputRowNum = 3 To lastRowOutput
If wsOutput.Cells(outputRowNum, 1).Value = downTimeDate And wsOutput.Cells(outputRowNum, 2).Value = workCenter Then
' Loop through time slots in Summary
For j = 5 To lastColOutput
timeSlot = wsOutput.Cells(2, j).Value
slotStart = ConvertTimeSlotToNumber(Left(timeSlot, 2))
slotEnd = ConvertTimeSlotToNumber(Right(timeSlot, 2))
' Fix: Ensure 23-00 slot is handled correctly
If slotEnd = 0 Then slotEnd = 24 ' Handle midnight transition
' Check if fault code falls within time slot
If (startTime < slotEnd And endTime > slotStart) Then
' Store fault codes uniquely
If Not uniqueFaultCodes.Exists(outputRowNum & "-" & j) Then
uniqueFaultCodes(outputRowNum & "-" & j) = faultCode
Else
If InStr(1, uniqueFaultCodes(outputRowNum & "-" & j), faultCode, vbTextCompare) = 0 Then
uniqueFaultCodes(outputRowNum & "-" & j) = uniqueFaultCodes(outputRowNum & "-" & j) & ", " & faultCode
End If
End If
End If
Next j
End If
Next outputRowNum
SkipFaultRow:
Next i
' Loop through Cutting sheet (Yield Summation)
For i = 2 To lastRowCutting
postingDate = wsCutting.Cells(i, 5).Value
workCenter = Trim(wsCutting.Cells(i, 14).Value)
department = Trim(wsCutting.Cells(i, 30).Value)
bayNo = Trim(wsCutting.Cells(i, 31).Value)
If IsNumeric(wsCutting.Cells(i, 8).Value) And wsCutting.Cells(i, 8).Value <> "" Then
yieldSum = CDbl(wsCutting.Cells(i, 8).Value)
Else
yieldSum = 0
End If
timeSlot = wsCutting.Cells(i, 28).Value
If Not IsDate(postingDate) Then GoTo SkipYieldRow
postingDate = CDate(postingDate)
' Loop through Summary to find a match
For outputRowNum = 3 To lastRowOutput
If wsOutput.Cells(outputRowNum, 1).Value = postingDate And wsOutput.Cells(outputRowNum, 2).Value = workCenter And wsOutput.Cells(outputRowNum, 3).Value = department And wsOutput.Cells(outputRowNum, 4).Value = bayNo Then
' Loop through time slots
For j = 5 To lastColOutput
If wsOutput.Cells(2, j).Value = timeSlot Then
' Accumulate Yield in Dictionary
If Not yieldDict.Exists(outputRowNum & "-" & j) Then
yieldDict(outputRowNum & "-" & j) = yieldSum
Else
yieldDict(outputRowNum & "-" & j) = yieldDict(outputRowNum & "-" & j) + yieldSum
End If
Exit For
End If
Next j
End If
Next outputRowNum
SkipYieldRow:
Next i
' Populate the Summary sheet with final combined values
For outputRowNum = 3 To lastRowOutput
For j = 5 To lastColOutput
Dim key As String
key = outputRowNum & "-" & j
' Fetch stored values
existingData = ""
If uniqueFaultCodes.Exists(key) Then existingData = uniqueFaultCodes(key)
If yieldDict.Exists(key) Then yieldSum = yieldDict(key) Else yieldSum = 0
' Combine yield and fault codes in the same cell
If yieldSum > 0 And existingData <> "" Then
combinedValue = yieldSum & ", " & existingData
ElseIf yieldSum > 0 Then
combinedValue = yieldSum
ElseIf existingData <> "" Then
combinedValue = existingData
Else
combinedValue = ""
End If
wsOutput.Cells(outputRowNum, j).Value = combinedValue
Next j
Next outputRowNum
MsgBox "Fault codes and Yield(KG) updated successfully!", vbInformation
End Sub
' Convert time slot (e.g., "07" -> 7.0, "23" -> 23.0)
Function ConvertTimeSlotToNumber(hourStr As String) As Double
ConvertTimeSlotToNumber = CDbl(TimeValue(hourStr & ":00"))
End Function
