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

RAJESH1960 · Apr 21, 2022

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 · Apr 22, 2022

that's a pivottable, except for the rate%

Combine amounts of duplicate Invoice Numbers.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

23

24

25

26

Waarden

27

Trade/Legal name

GSTIN of supplier

Invoice number

A

Invoice Date

B

C

D

Rate(%)

Som van Taxable Value (₹)

Som van Integrated Tax(₹)

Som van Central Tax(₹)

Som van State/UT Tax(₹)

28

January

24ABCD1234

100

(leeg)

02/08/2021

(leeg)

2.978,40

148,92

0

29

101

(leeg)

05/08/2021

(leeg)

10.857,90

704,74

0

30

102

(leeg)

22/06/2021

(leeg)

38.619,02

2.671,73

0

31

103

(leeg)

09/12/2021

(leeg)

1.013,63

50,68

0

32

104

(leeg)

09/12/2021

(leeg)

10.627,20

1.275,26

0

33

105

(leeg)

11/12/2021

(leeg)

18.817,94

1.147,96

0

34

107

(leeg)

23/06/2021

(leeg)

2.822,00

338,64

0

35

February

24ABCD1234

100

(leeg)

15/09/2021

(leeg)

49.226,00

2.461,30

0

36

March

27ABCD1235

101

(leeg)

23/09/2021

(leeg)

26.000,00

0,00

1280

37

April

29ABCD1236

101

(leeg)

23/08/2021

(leeg)

33.721,43

0,00

843,04

38

May

29ABCD1237

101

(leeg)

12/08/2021

(leeg)

17.898,00

894,90

0

39

September

09ABCD1241

101

(leeg)

08/04/2021

(leeg)

1.588,47

0,00

155,71

40

October

29ABCD1242

101

(leeg)

29/06/2021

(leeg)

2.585,00

129,25

0

41

November

27ABCD1243

101

(leeg)

22/04/2021

(leeg)

26.290,00

3.673,20

0

42

December

06ABCD1244

101

(leeg)

12/04/2021

(leeg)

6.526,46

0,00

726,38

43

Eindtotaal

249.571,45

13.496,58

3005,13

44

45

Expected Result

RAJESH1960 · Apr 22, 2022

BSALV said:
that's a pivottable, except for the rate%
Combine amounts of duplicate Invoice Numbers.xlsm
A B C D E F G H I J K L M
23
24
25
26 Waarden
27 Trade/Legal name GSTIN of supplier Invoice number A Invoice Date B C D Rate(%) Som van Taxable Value (₹) Som van Integrated Tax(₹) Som van Central Tax(₹) Som van State/UT Tax(₹)
28 January 24ABCD1234 100 (leeg) 02/08/2021 (leeg) (leeg) (leeg) 2.978,40 148,92 0 0
29 101 (leeg) 05/08/2021 (leeg) (leeg) (leeg) 10.857,90 704,74 0 0
30 102 (leeg) 22/06/2021 (leeg) (leeg) (leeg) 38.619,02 2.671,73 0 0
31 103 (leeg) 09/12/2021 (leeg) (leeg) (leeg) 1.013,63 50,68 0 0
32 104 (leeg) 09/12/2021 (leeg) (leeg) (leeg) 10.627,20 1.275,26 0 0
33 105 (leeg) 11/12/2021 (leeg) (leeg) (leeg) 18.817,94 1.147,96 0 0
34 107 (leeg) 23/06/2021 (leeg) (leeg) (leeg) 2.822,00 338,64 0 0
35 February 24ABCD1234 100 (leeg) 15/09/2021 (leeg) (leeg) (leeg) 49.226,00 2.461,30 0 0
36 March 27ABCD1235 101 (leeg) 23/09/2021 (leeg) (leeg) (leeg) 26.000,00 0,00 1280 1280
37 April 29ABCD1236 101 (leeg) 23/08/2021 (leeg) (leeg) (leeg) 33.721,43 0,00 843,04 843,04
38 May 29ABCD1237 101 (leeg) 12/08/2021 (leeg) (leeg) (leeg) 17.898,00 894,90 0 0
39 September 09ABCD1241 101 (leeg) 08/04/2021 (leeg) (leeg) (leeg) 1.588,47 0,00 155,71 155,71
40 October 29ABCD1242 101 (leeg) 29/06/2021 (leeg) (leeg) (leeg) 2.585,00 129,25 0 0
41 November 27ABCD1243 101 (leeg) 22/04/2021 (leeg) (leeg) (leeg) 26.290,00 3.673,20 0 0
42 December 06ABCD1244 101 (leeg) 12/04/2021 (leeg) (leeg) (leeg) 6.526,46 0,00 726,38 726,38
43 Eindtotaal 249.571,45 13.496,58 3005,13 3005,13
44
45
Expected Result

BSALV/ Thanks for the response. I am trying to get it with the help of a code/ It is just a part of the code which will be added to the already existing code.. There is a lot of editing and manual work involved in using pivot table, like filling the blank ,etc. Getting the values of one invoice number with different rates in one row is also not possible. Also, the sheet is a merged one with headings in different rows. I have to get the result without changing the original data sheet.

bebo021999 · Apr 22, 2022

VBA solution:

VBA Code:

Option Explicit
Sub add()
Dim lr&, k&, j&, id As String, item As String, cell As Range, s, key, arr(), ws As Worksheet
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In Sheets
    If ws.Name = "PORTAL" Then ws.Delete ' delete previous version of sheet PORTAL
Next
Application.DisplayAlerts = True
Worksheets("2B").Activate
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In Range("A7:A" & lr)
        id = cell & "|" & cell.Offset(0, 1) & "|" & cell.Offset(0, 2) ' column A&B&C combination
        item = cell.Offset(0, 4) & "|" & cell.Offset(0, 8) & "|" & cell.Offset(0, 9) & "|" & cell.Offset(0, 10) _
                & "|" & cell.Offset(0, 11) & "|" & cell.Offset(0, 12) & "|" & cell.Offset(0, 13)
            If Not dic.exists(id) Then
                dic.add id, item
            Else
                s = Split(dic(id), "|")
                dic(id) = s(0) & "|" & s(1) & "+" & cell.Offset(0, 8) & "|" & _
                s(2) + cell.Offset(0, 9) & "|" & _
                s(3) + cell.Offset(0, 10) & "|" & _
                s(4) + cell.Offset(0, 11) & "|" & _
                s(5) + cell.Offset(0, 12) & "|" & _
                s(6) + cell.Offset(0, 13)
            End If
    Next
Sheets.add after:=ActiveSheet
ActiveSheet.Name = "PORTAL"
Worksheets("2B").Range("A1:V6").Copy Range("A1")
ReDim arr(1 To dic.Count, 1 To 22)
    For Each key In dic.keys
        k = k + 1
        For j = 1 To 22
            Select Case j
                Case 1, 2, 3
                    arr(k, j) = Split(key, "|")(j - 1) & IIf(j = 3, "-Total", "")
                Case 5
                    arr(k, j) = Split(dic(key), "|")(0)
                Case 9, 10, 11, 12, 13
                    arr(k, j) = Split(dic(key), "|")(j - 8)
            End Select
        Next
    Next
With Range("A7").Resize(dic.Count, 22)
    .Value = arr
    .EntireColumn.AutoFit
    .Columns(9).HorizontalAlignment = xlCenter
End With
Range("J7:M" & dic.Count+6).NumberFormat = "#,##0.00"
Application.ScreenUpdating = True
End Sub

RAJESH1960 · Apr 23, 2022

Wow!! Unbelievable. It's magic. The things you can do with VBA coding is just incredible. If done right in the first time, one hour of manual work done in just 1 second. When done manually, there may be mistakes and I have to do it again from the beginning and that would take more than an hour. But this is done just so perfect with this code.
The date has always been a problem in most of my projects, but it is ok in this case since I have to match the amounts only.
Thank you very much Bebo02 for the solution and your valuable time to share this code.

bebo021999 · Apr 23, 2022

Nice to hear it works and thank for the feedback!

RAJESH1960 · Apr 23, 2022

RAJESH1960 · Apr 23, 2022

Bebo02. I have just posted a New post query which is connected to the above post. As you have understood it I hope you will be able to solve it easily.

BSALV · Apr 23, 2022

just a small improvement/adjustment to speed up
* read that table to an array (with ".value2" because it contains dates)
* instead of just 1 element in the items, do them all, in an array. So you don't have to split later.
That code is a little bit more complicated, but if you have to do a lot of rows, it'll need a fraction of the time to execute.

RAJESH1960 · Apr 23, 2022

RAJESH1960 said:
Bebo02. I have just posted a New post query which is connected to the above post. As you have understood it I hope you will be able to solve it easily.

This is regarding the above message. There is no problem at all. Sorry. It was my mistake to have tried in a different data with duplicates.

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

RAJESH1960

Banned for repeated rules violations

Excel Facts

BSALV

Banned user

RAJESH1960

Banned for repeated rules violations

bebo021999

Well-known Member

RAJESH1960

Banned for repeated rules violations

bebo021999

Well-known Member

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

BSALV

Banned user

RAJESH1960

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

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

Banned for repeated rules violations

Excel Facts

Banned user

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Banned for repeated rules violations

Banned user

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock