Macro with button to number items within an invoice

[kthakur]

Hi everybody!

Here's a business issue that I am having -

My business needs to send a certain report to the tax authorities that shows all our purchases (inward data register).
When our ERP system creates this report, we get a big excel file which has all the information pertaining to the purchase invoices that have been raised on our organisation.
However, most invoices usually do have more than one line item in them BUT our software does not have the ability to tag these line items separately.
Given we need to mention unique line items for each invoice in this report, we are currently doing so manually which needless to say is very time-consuming.
I wanted to surface this on the forum and see if someone can suggest a macro code which would basically go through the entire column of "Invoice Number". Every time the code encounters an invoice number it should write "1" in front of it in the line items column. IF the same invoice number appears again, the code should write "2" in the line-item column. Similarly, if a third occurrence happens, write "3".
It is very important to understand that every invoice number WILL have at least "1" in front of it in the line item column. IFF the same invoice number appears again, only then would it write "2".

I am attaching a dummy file with the fields and some dummy values.

If this could be done it would save my organisations hundreds of man hours every month.

Any suggestions are very welcome and thanks in advance for the help!

[Marc L]

Hi !

As a very beginner formula yet does the job : in A2 cell just enter =COUNTIF(B$2:B2,B2) then copy down …

[PacificPR]

You refer

Sub Invoice_Number()     
    Dim sArr(), dArr(), I As Long, N As Long
sArr = Range("B2", Range("B" & Rows.Count).End(3)).Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 1)
For I = 1 To UBound(sArr, 1)
    N = Application.CountIf(Range("B2:B" & I + 1), Range("B" & I + 1))
    dArr(I, 1) = Format(N, "00000#")
Next I
Range("A2", Range("A" & Rows.Count).End(3)).ClearContents
Range("A2").Resize(I - 1) = dArr
End Sub  

[kthakur]

Thanks so much for the prompt reply.

Will try and see if this suffices for the entire list of invoices.

[Marc L]

So if really a code is needed, just directly applying the beginner formula :

Sub Demo1() 
    With Sheet1.Range("A2", Sheet1.Cells(Rows.Count, 2).End(xlUp)(1, 0)) 
        .NumberFormat = "000000" 
        .Formula = "=COUNTIF(B$2:B2,B2)" 
        .Formula = .Value 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[kthakur]

Thanks Pacific PR!!

[Marc L]

Thanks for the rep' !

Code in my previous post was edited …