Option Explicit
Sub New_FINAL_RECON_AUDIT_PROOF()
Dim wsPR As Worksheet, ws2B As Worksheet
Dim wsRec As Worksheet, wsMatch As Worksheet, wsNotMatch As Worksheet
Set wsPR = Sheets("Purchase_Register")
Set ws2B = Sheets("GSTR-2B_Data")
Set wsRec = Sheets("Reconciliation")
Set wsMatch = Sheets("Matched_Data")
Set wsNotMatch = Sheets("Not_Matched")
' Clear old data
wsRec.Cells.Clear
wsMatch.Cells.Clear
wsNotMatch.Cells.Clear
' Headers
wsRec.Range("A1:H1") = Array("GSTIN", "Supplier", "Invoice", "Books Total", "2B Total", "Diff", "Status", "Remarks")
wsMatch.Range("A1:G1") = Array("GSTIN", "Supplier", "Invoice", "Books", "2B", "Status", "Type")
wsNotMatch.Range("A1:G1") = Array("GSTIN", "Supplier", "Invoice", "Books", "2B", "Status", "Reason")
' Header Formatting (ONLY HEADER)
With wsRec.Range("A1:H1")
.Font.Bold = True
.Interior.Color = RGB(0, 112, 192)
.Font.Color = RGB(255, 255, 255)
End With
' ===============================
' BUILD BOOKS DICTIONARY (GROUPED)
' ===============================
Dim dictBooks As Object
Set dictBooks = CreateObject("Scripting.Dictionary")
Dim i As Long
Dim key As Variant
For i = 2 To wsPR.Cells(wsPR.Rows.count, 1).End(xlUp).Row
Dim gst As String, inv As String
Dim valB As Double, supplier As String
gst = UCase(Trim(wsPR.Cells(i, 1).Value))
supplier = wsPR.Cells(i, 2).Value
'inv = NormalizeInvoice(wsPR.Cells(i, 3).Value)
inv = GetInvoiceNumber_Final(wsPR.Cells(i, 3).Value)
If Trim(inv) = "" Then GoTo NextBooksRow
valB = GetTaxValue_new(wsPR, i)
key = gst & "|" & inv
If dictBooks.exists(key) Then
dictBooks(key)(0) = dictBooks(key)(0) + valB
Else
'dictBooks.Add key, Array(valB, supplier, gst, inv) this is changed by me now
dictBooks.Add key, Array(valB, supplier, gst, inv, 1)
If dictBooks.exists(key) Then
dictBooks(key)(0) = dictBooks(key)(0) + valB
dictBooks(key)(4) = dictBooks(key)(4) + 1 ' ?? count
Else
dictBooks.Add key, Array(valB, supplier, gst, inv, 1)
End If
End If
NextBooksRow:
Next i
' ===============================
' PROCESS 2B DATA
' ===============================
Dim rowRec As Long: rowRec = 2
Dim rowM As Long: rowM = 2
Dim rowN As Long: rowN = 2
For i = 2 To ws2B.Cells(ws2B.Rows.count, 1).End(xlUp).Row
Dim gst2B As String, inv2B As String
Dim val2B As Double
gst2B = UCase(Trim(ws2B.Cells(i, 1).Value))
'inv2B = NormalizeInvoice(ws2B.Cells(i, 3).Value)
inv2B = GetInvoiceNumber_Final(ws2B.Cells(i, 3).Value)
If Trim(inv2B) = "" Then
wsRec.Cells(rowRec, 1) = gst2B
wsRec.Cells(rowRec, 2) = ws2B.Cells(i, 2).Value
wsRec.Cells(rowRec, 3) = ws2B.Cells(i, 3).Value
wsRec.Cells(rowRec, 7) = "Invalid"
wsRec.Cells(rowRec, 8) = "Invoice Format Error"
rowRec = rowRec + 1
GoTo Next2BRow
End If
val2B = val(ws2B.Cells(i, 6)) + val(ws2B.Cells(i, 7)) + val(ws2B.Cells(i, 8))
key = gst2B & "|" & inv2B
Dim booksVal As Double: booksVal = 0
Dim supplierName As String: supplierName = ""
If dictBooks.exists(key) Then
booksVal = dictBooks(key)(0)
supplierName = dictBooks(key)(1)
Else
' ? TAKE SUPPLIER FROM 2B WHEN NOT IN BOOKS
supplierName = ws2B.Cells(i, 2).Value
End If
Dim diff As Double
'diff = booksVal - val2B
diff = val2B - booksVal
Dim status As String, remark As String, mType As String
' ===============================
' ? FINAL CORRECT LOGIC
' ===============================
If dictBooks.exists(key) Then
If Abs(diff) <= 1 Then
status = "Matched"
' ?? CHECK SPLIT
If dictBooks(key)(4) > 1 Then
remark = "OK (Split Invoice)"
mType = "Split Match"
Else
remark = "OK"
mType = "Exact Match"
End If
Else
status = "Mismatch"
remark = "Value Difference"
mType = "Invoice Match"
End If
Else
status = "Not Found"
remark = "Missing in Books"
End If
' ===============================
' RECONCILIATION OUTPUT
' ===============================
wsRec.Cells(rowRec, 1) = gst2B
wsRec.Cells(rowRec, 2) = supplierName
wsRec.Cells(rowRec, 3) = inv2B
wsRec.Cells(rowRec, 4) = booksVal
wsRec.Cells(rowRec, 5) = val2B
wsRec.Cells(rowRec, 6) = diff
wsRec.Cells(rowRec, 7) = status
wsRec.Cells(rowRec, 8) = remark
' ===============================
' COLOR LOGIC (FINAL CORRECT)
' ===============================
Dim rngRow As Range
Set rngRow = wsRec.Range(wsRec.Cells(rowRec, 1), wsRec.Cells(rowRec, 8))
' Reset formatting
rngRow.Font.Bold = False
rngRow.Font.ColorIndex = xlAutomatic
If status = "Matched" Then
If Trim(UCase(remark)) Like "OK*" Then
' ?? BLUE (Matched + OK)
rngRow.Font.Bold = True
rngRow.Font.Color = RGB(0, 0, 255)
End If
Else
' ?? RED (Issues)
rngRow.Font.Bold = True
rngRow.Font.Color = RGB(255, 0, 0)
End If
' ===============================
' MATCH / NOT MATCH SHEETS
' ===============================
If status = "Matched" Then
wsMatch.Cells(rowM, 1) = gst2B
wsMatch.Cells(rowM, 2) = supplierName
wsMatch.Cells(rowM, 3) = inv2B
wsMatch.Cells(rowM, 4) = booksVal
wsMatch.Cells(rowM, 5) = val2B
wsMatch.Cells(rowM, 6) = status
wsMatch.Cells(rowM, 7) = mType
rowM = rowM + 1
Else
wsNotMatch.Cells(rowN, 1) = gst2B
wsNotMatch.Cells(rowN, 2) = supplierName
wsNotMatch.Cells(rowN, 3) = inv2B
wsNotMatch.Cells(rowN, 4) = booksVal
wsNotMatch.Cells(rowN, 5) = val2B
wsNotMatch.Cells(rowN, 6) = status
wsNotMatch.Cells(rowN, 7) = remark
rowN = rowN + 1
End If
rowRec = rowRec + 1
Next2BRow:
Next i
MsgBox "Final Audit Report Ready", vbInformation
End Sub
