Extract Invoice Number to Number Format

[sgmpatnaik]

Hi, Good Morning Every One

actually i am a tax Consultant and i have to cross check the books of records with GST , I have prepared one excel file to Reconcile the data from GST to Clients Data here i am facing an problem, that is i would like to extract the numbers of GST invoice to numbers format but i fail to ,so in this time i remember my family members
and i came here with full hope , i tried lots of function codes but i fail and finally i came here because here my teachers and ninjas are here, i hope you understand the issue and solves my problem

i would like to create a function code like GetInvoice_Number()

for your knowledge i have attached the Sample file

Thanking You

Mr. Patnaik

 

[vletm]

Instead of a function ... could You use something like?
Your given data have many combinations/variations of ... two years ... or how?
My sample:
I collect those to E-column.
G-column shows my results.
If there are 'any challenges' ... those rows will mark in I-column.
Of course, Your used layout needs to take care.
Usage: press [ Do It ]-button

 

[sgmpatnaik]

Instead of a function ... could You use something like?
Your given data have many combinations/variations of ... two years ... or how?
My sample:
I collect those to E-column.
G-column shows my results.
If there are 'any challenges' ... those rows will mark in I-column.
Of course, Your used layout needs to take care.
Usage: press [ Do It ]-button

Hi, sorry for my delay replay. i completely gone through your sample file and i checked there you have taken a helper column E there we have to mention the ignore methods to get the invoice number, after that if we run the code which you given the button do it then it extract the invoice numbers base with Column E then we are getting the Invoice Numbers exactly, but it is difficult to mark the Column E for Away records when the records are 1k + another point is if we have any work pressure on that time we can mention the Away Records but it is highly impossible when we have a work load. Actually i write a code for get the Numbers to extract but that is not extracting for 100 %

For your knowledge i have attached the same file and mentioned the results in Sheet 2, at present i mentioned the wrong generating numbers in the column F to I

i am waiting for the perfect result or any good suggestion from you and my old Teachers

Thanking You

 

[vletm]

That was a sample.
A helper column can be ... any free column!
... even extraction column could be same as 'source' (Your A-column).
What is ... difficult?
There could be ... any number of data and so on.
I modified my sample based my previous sample.

 

[sgmpatnaik]

Here i am running a code for Data Reconciliation with sheets that's why i want a function code for GetInvoice Number

hope you understand

Thank You

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

 

[vletm]

If Your ... Invoice codes ... are in other column than A then
let me know - where are those? I can modify my code.

In the beginning of Your above ... code ...
You should add my code and
You could get those 'shorter version' in that column.
... which You could get without any ... functions.
if Your function won't work ... my code works...
if my guessed logic is as it should be.
... which You've skipped to verify.

Of course, You could modify my macro to Your function Yourself.

 

[Gateway2026]

Hi past in new module this code; use the formula example =GetInvoice_Number(A2)

Function GetInvoice_Number(inv As String) As String
    Dim oRegex    As Object
    Dim oMatches  As Object
    Dim sParts()  As String
    Dim sResult   As String
    Dim sPart     As String
    Dim i As Integer, j As Integer
    Dim y1 As Integer, y2 As Integer
    Dim bIsFY As Boolean
    Dim nCount As Integer
    Dim sLast As String, sSecLast As String
    Dim sRemain As String

    Set oRegex = CreateObject("VBScript.RegExp")
    oRegex.Global = True

    inv = Trim(inv)
    If inv = "" Then GetInvoice_Number = "": Exit Function

    If InStr(inv, "/") > 0 Then
        sParts = Split(inv, "/")
        For i = UBound(sParts) To 0 Step -1
            sPart = Trim(sParts(i))
            bIsFY = False
            oRegex.Pattern = "^(\d{2,4})-(\d{2,4})$"
            If oRegex.Test(sPart) Then
                Set oMatches = oRegex.Execute(sPart)
                y1 = CInt(oMatches(0).SubMatches(0)) Mod 100
                y2 = CInt(oMatches(0).SubMatches(1)) Mod 100
                If y2 = (y1 + 1) Mod 100 Then bIsFY = True
            End If
            If Not bIsFY Then
                oRegex.Pattern = "\d+"
                If oRegex.Test(sPart) Then
                    Set oMatches = oRegex.Execute(sPart)
                    sResult = ""
                    For j = 0 To oMatches.Count - 1
                        sResult = sResult & oMatches(j).Value
                    Next j
                    GetInvoice_Number = sResult
                    Exit Function
                End If
            End If
        Next i
    Else
        sParts = Split(inv, "-")
        nCount = UBound(sParts) + 1
        oRegex.Pattern = "^\d{2,4}$"
        Do While nCount >= 2
            sLast    = Trim(sParts(nCount - 1))
            sSecLast = Trim(sParts(nCount - 2))
            If oRegex.Test(sLast) And oRegex.Test(sSecLast) Then
                y1 = CInt(sSecLast) Mod 100
                y2 = CInt(sLast) Mod 100
                If y2 = (y1 + 1) Mod 100 Then
                    nCount = nCount - 2
                Else
                    Exit Do
                End If
            Else
                Exit Do
            End If
        Loop
        sRemain = ""
        For i = 0 To nCount - 1
            If i > 0 Then sRemain = sRemain & "-"
            sRemain = sRemain & sParts(i)
        Next i
        oRegex.Pattern = "\d+"
        If oRegex.Test(sRemain) Then
            Set oMatches = oRegex.Execute(sRemain)
            sResult = ""
            For j = 0 To oMatches.Count - 1
                sResult = sResult & oMatches(j).Value
            Next j
            GetInvoice_Number = sResult
        Else
            GetInvoice_Number = ""
        End If
    End If

End Function

 

[sgmpatnaik]

Ah Great Work the both suggestions,

Thank You Friends for your support, i did my job with your support, if any help need then i will knock the door again

Thank You

 

[Gateway2026]

you are welcome