Combine amounts of duplicate Invoice numbers with the help of a code

[RAJESH1960]

Hello guys,
I have to convert this sheet compatible to run the code shared by JohnnyL. In the 2B sheet there are rows which have duplicate Invoice number due to multiple class of tax in the Rate column. I have to remove the duplicate invoice numbers in which GSTIN, Trade Name and Invoice date have also the same duplicate values. But before removing them rows which have duplicate invoice number , the amounts in Taxable value, Integrated Tax, Central tax and central tax have to be added and displayed in the one row which is not deleted. To know which rows have multiple class of tax, the rates column should display the combined values of rate of the rows deleted. Finally, all the invoice numbers should be joined with the text "-Total" after the invoice number. The code should display the result in a new sheet with the name "PORTAL", so that I don't have to edit the code connected to this sheet. Thank you in advance.
Combine amounts of duplicate Invoice Numbers.xlsm

 

[BSALV]

just as demo of above, the actual data is copied 10 times to have more lines.
with 30k lines, my macro needs 1/3 of the time of your macro.
My regional setting use "dd/mm/jj" as date and it keeps that as date.

example file

Sub add_BS()
     t = Timer
     Set dic = CreateObject("scripting.dictionary")
     With Worksheets("2B")
          lr = .Cells(Rows.Count, "A").End(xlUp).Row
          If lr < 7 Then MsgBox "no data", vbCritical: Exit Sub
          arr = .Range("A7:M" & lr).Value2
     End With

     For r = 1 To UBound(arr)
          id = Join(Array(arr(r, 1), arr(r, 2), arr(r, 3)), "|")     ' column A&B&C combination
          If Not dic.exists(id) Then
               dic.add id, Application.Index(arr, r, 0)     'add the whole row as item
          Else
               it = dic(id)
               it(9) = it(9) & "+" & arr(r, 9)     'no SUM, add as string
               For i = 10 To UBound(it)
                    it(i) = it(i) + arr(r, i)     'sum
               Next
               dic(id) = it
          End If
     Next

     arr1 = Application.Index(dic.items, 0, 0)     'read the items
     Sheets("portal").Range("A30").Resize(UBound(arr1), UBound(arr1, 2)).Value = arr1     'write them to the sheet

     MsgBox Timer - t
End Sub

 

[RAJESH1960]

just as demo of above, the actual data is copied 10 times to have more lines.
with 30k lines, my macro needs 1/3 of the time of your macro.
My regional setting use "dd/mm/jj" as date and it keeps that as date.

example file

Sub add_BS()
     t = Timer
     Set dic = CreateObject("scripting.dictionary")
     With Worksheets("2B")
          lr = .Cells(Rows.Count, "A").End(xlUp).Row
          If lr < 7 Then MsgBox "no data", vbCritical: Exit Sub
          arr = .Range("A7:M" & lr).Value2
     End With

     For r = 1 To UBound(arr)
          id = Join(Array(arr(r, 1), arr(r, 2), arr(r, 3)), "|")     ' column A&B&C combination
          If Not dic.exists(id) Then
               dic.add id, Application.Index(arr, r, 0)     'add the whole row as item
          Else
               it = dic(id)
               it(9) = it(9) & "+" & arr(r, 9)     'no SUM, add as string
               For i = 10 To UBound(it)
                    it(i) = it(i) + arr(r, i)     'sum
               Next
               dic(id) = it
          End If
     Next

     arr1 = Application.Index(dic.items, 0, 0)     'read the items
     Sheets("portal").Range("A30").Resize(UBound(arr1), UBound(arr1, 2)).Value = arr1     'write them to the sheet

     MsgBox Timer - t
End Sub

BSALV. First of all thanks for trying to help me to make the code run in the minimum possible time. You are using 365 I assume, as I have downloaded your worksheet and checked. I have excel 2019. As the query is already solved and you have taken a great effort to complete it, I am a bit confused with the result. The 2B sheet should be as it is in the original form. Secondly if there are 30,000 rows in 2B the result has to be at least 29, 000 row in Portal sheet, assuming that there may be 1000 rows of same invoice number with different rates. I think that is because you have missed to change the Invoice number in the additional data. I have saved your code also in my code bank. You never know when and which line will be helpful in future. Thanks once again.

 

[RAJESH1960]

@bebo021999 . I need your help once again on the same query. I am trying to get the values from the Cess amount column from 2B to Portal but I am unable to do it. Can you please edit and add one more column in the code to add and get the value.? I have made some editing in your code and it is working fine. You will just have to get the Cess amount as you did with Integrated Tax (₹), Central Tax (₹), State/UT tax (₹) and Taxable Value (₹).
This is the link to the updated worksheet. I would really appreciate it if you leave a comment at each line. It would really be helpful for me to understand and edit for future references.
Get cess sum 2B.xlsm

 

[RAJESH1960]

Got it corrected now. It is solved.