VBA - finding duplicates, merge and sum rows

J

Jakub92

New Member
Joined
Sep 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Team

I am looking for VBA code that will remove duplicates by column (A&B) and sum and merge for C after clearing cells for (A&B) and merge duplicate cells.
If there are blank rows in column C, they must be merge with the unique rows in the column (A&B).
below sheets:

Worksheet before
Zeszyt1
ABC
1Day of the week Name Meters
201.07.2021TOM28800
301.07.2021JOHN60800
402.07.2021TOM38400
502.07.2021MATT16000
602.07.2021MATT27200
703.07.2021DAMIAN62400
803.07.2021TOM60500
904.07.2021DAMIAN16000
1004.07.2021DAMIAN43400
1104.07.2021TOM62850
1205.07.2021PETER68600
1305.07.2021JACOB63000
1406.07.2021PETER9400
1506.07.2021PETER53200
1606.07.2021JACOB8400
1706.07.2021JACOB13800
1806.07.2021JACOB19300
1907.07.2021JOHN68400
2007.07.2021PETER65600
2108.07.2021PETER10700
2208.07.2021JOHN14400
2308.07.2021JOHN15600
2408.07.2021JOHN43400
2508.07.2021PETER18200
2608.07.2021PETER15400
2708.07.2021PETER29600
2809.07.2021TOM56800
2909.07.2021JOHN6000
3009.07.2021JOHN39100
3109.07.2021JOHN
3210.07.2021TOM25600
3310.07.2021JOHN51200
3411.07.2021TOM48000
3512.07.2021JACOB35200
3612.07.2021JACOB10200
3712.07.2021TOM31200
3813.07.2021MIKE40000
3913.07.2021PETER40550
4014.07.2021MIKE36400
4114.07.2021PETER20100
4214.07.2021PETER6000
4314.07.2021PETER
4415.07.2021JOHN5100
4515.07.2021JOHN
4615.07.2021JOHN6000
4715.07.2021MIKE6000
4815.07.2021MIKE22500
4916.07.2021JOHN19500
5016.07.2021JOHN3000
5116.07.2021JOHN4500
5216.07.2021JOHN5900
5316.07.2021JOHN4000
5416.07.2021JOHN4200
5516.07.2021MIKE57950
5617.07.2021ROBERT40000
5717.07.2021ROBERT4200
5817.07.2021JOHN67200
5918.07.2021ROBERT11150
6018.07.2021ROBERT34350
6118.07.2021ROBERT3200
6218.07.2021JOHN39000
6319.07.2021PETER41000
6419.07.2021ROBERT51200
6520.07.2021PETER12200
6620.07.2021PETER36000
6720.07.2021ROBERT43300
6820.07.2021ROBERT3800
6921.07.2021MIKE27600
7021.07.2021MIKE1500
7121.07.2021PETER27000
7221.07.2021PETER31500
7322.07.2021MIKE13500
7422.07.2021MIKE9000
7522.07.2021MIKE2950
7622.07.2021PETER2000
7722.07.2021PETER35350
7823.07.2021JOHN77000
7923.07.2021MIKE43100
8023.07.2021MIKE6000
8124.07.2021JOHN34000
8224.07.2021MIKE31300
8325.07.2021PETER7350
8425.07.2021PETER2700
8525.07.2021PETER19600
8625.07.2021JOHN67200
8726.07.2021ROBERT58200
8826.07.2021MATT64400
8927.07.2021PETER24050
9027.07.2021PETER15100
9127.07.2021ROBERT55750
9228.07.2021PETER54300
9328.07.2021ROBERT26700
9428.07.2021ROBERT6000
9528.07.2021ROBERT9000
9629.07.2021DAMIAN27000
9729.07.2021DAMIAN30000
9829.07.2021PETER10300
9929.07.2021PETER25600
10030.07.2021DAMIAN44800
10130.07.2021DAMIAN3200
10230.07.2021JACOB48000
10330.07.2021JACOB
10431.07.2021JOHN21000
10531.07.2021JOHN19500
10631.07.2021JOHN5600
10731.07.2021DAMIAN35350
10831.07.2021DAMIAN25200
Arkusz2


Column A should look for all duplicates in its cells and merge them on a condition that the content of the cells in Column B at the same row level can also be merged.
Column C should also look for all duplicates in its cells, merge and sum them on a condition that Column A and Column B had cells that were merged at the same row level.


After using VBA code

Zeszyt1
ABC
1Day of the week Name Sum of meters after duplicate cells A and B are connected
201.07.2021TOM28800
301.07.2021JOHN60800
402.07.2021TOM38400
502.07.2021MATT43200
6
703.07.2021DAMIAN62400
803.07.2021TOM60500
904.07.2021DAMIAN59400
10
1104.07.2021TOM62850
1205.07.2021PETER68600
1305.07.2021JACOB63000
1406.07.2021PETER62600
15
1606.07.2021JACOB41500
17
18
1907.07.2021JOHN68400
2007.07.2021PETER76300
21
2208.07.2021JOHN73400
23
24
2508.07.2021PETER63200
26
27
2809.07.2021TOM56800
2909.07.2021JOHN45100
30
31
3210.07.2021TOM25600
3310.07.2021JOHN51200
3411.07.2021TOM48000
3512.07.2021JACOB45400
36
3712.07.2021TOM31200
3813.07.2021MIKE40000
3913.07.2021PETER40550
4014.07.2021MIKE36400
4114.07.2021PETER26100
42
43
4415.07.2021JOHN11100
45
46
4715.07.2021MIKE28500
48
4916.07.2021JOHN41100
50
51
52
53
54
5516.07.2021MIKE57950
5617.07.2021ROBERT44200
57
5817.07.2021JOHN67200
5918.07.2021ROBERT48700
60
61
6218.07.2021JOHN39000
6319.07.2021PETER41000
6419.07.2021ROBERT51200
6520.07.2021PETER48200
66
6720.07.2021ROBERT47100
68
6921.07.2021MIKE29100
70
7121.07.2021PETER58500
72
7322.07.2021MIKE25450
74
75
7622.07.2021PETER37350
77
7823.07.2021JOHN77000
7923.07.2021MIKE49100
80
8124.07.2021JOHN34000
8224.07.2021MIKE31300
8325.07.2021PETER29650
84
85
8625.07.2021JOHN67200
8726.07.2021ROBERT58200
8826.07.2021MATT64400
8927.07.2021PETER39150
90
9127.07.2021ROBERT55750
9228.07.2021PETER54300
9328.07.2021ROBERT41700
94
95
9629.07.2021DAMIAN57000
97
9829.07.2021PETER35900
99
10030.07.2021DAMIAN48000
101
10230.07.2021JACOB48000
103
10431.07.2021JOHN46100
105
106
10731.07.2021DAMIAN60550
108
Arkusz3


I hope I explained it well, thank you in advance for any help

Best regards
Jacob
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
I think this is quite simple. You can do it yourself by 3 steps follow:
Presume Sheet1 = The worksheet before dublicate, sheet2 = The worksheet after dublicate
1st. Copy Column A&B from Sheet1 to Sheet2, start at Column A
2nd. Using Remove Dublicates function in Column A&B of the sheet2
3rd. Use Sumifs at column C Sheet2 with sum range at Column C sheet1. There you go !!!
And if you want it done automatically, record a macro. We actually don't need VBA code for this case !
 
Upvote 0
Hi,

Please look this topic

VBA - Clear Duplicates and merge them by column // Empty cells should be ignored​

2nd. Using Remove Dublicates function in Column A&B of the sheet2
Click to expand...
Only Merged, because in column C I am totaling the counters after combining duplicate cells A and B

This is variable data and I would have to use a macro every now and then

for example
Zeszyt1
ABC
1606.07.2021JACOB8400
1706.07.2021JACOB13800
1806.07.2021JACOB19300
Arkusz2


If I merge a few rows in column C then I won't get a good result

Zeszyt1
ABC
1606.07.2021JACOB41500
17
18
Arkusz3



Step 1!
I rewrite "names" in the to a separate column.
Step 2!
I Sum how much day within month worked "name" in column B
I use formule =IFCOUNT
Step3!
I sum meter for everyone "names" in column B after
I use formule = SUMIFS
Step4!

The table below is for me.
The following table after use VBA code

Zeszyt1
EFG
1name sum day for "name"sum meters
2JOHN13681600
3TOM8352150
4MATT2107600
5DAMIAN5287350
6PETER14681400
7JACOB4197900
8MIKE8297800
9ROBERT7346850
102952650
11IFCOUNTSUMIFS
Arkusz3
Cell Formulas
RangeFormula
F2:F9F2=COUNTIF($B$2:$B$108,"="&E2)
G2:G9G2=SUMIFS($C$2:$C$108,$B$2:$B$108,"="&E2,$B$2:$B$108,"="&E2)
G10G10=SUM(G2:G9)


Only use function
Dont look at the cells formulas

Zeszyt1
EFG
1namesum day for "name"sum meters
2JOHN26681600
3TOM8352150
4MATT3107600
5DAMIAN9287350
6PETER27681400
7JACOB8197900
8MIKE13297800
9ROBERT13346850
10WRONG SUM DAY for "name2952650
11IFCOUNTSUMIFS
Arkusz2
Cell Formulas
RangeFormula
F2:F9F2=COUNTIF($B$2:$B$108,"="&E2)
G2:G9G2=SUMIFS($C$2:$C$108,$B$2:$B$108,"="&E2,$B$2:$B$108,"="&E2)
G10G10=SUM(G2:G9)
Named Ranges
NameRefers ToCells
_FilterDatabase=Arkusz2!$B$2:$B$108F2:G9
 
Upvote 0
Hi, @Jakub92
Welcome to Forum:
Try this:
VBA Code: 
Sub a1181462a()
Dim i As Long, j As Long
Dim va

Application.DisplayAlerts = False
Application.ScreenUpdating = False
va = Range("A1", Cells(Rows.Count, "C").End(xlUp))
For i = 2 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) & va(i, 2) = va(i - 1, 1) & va(i - 1, 2)
    i = i - 1
    
    If i <> j Then
        
        With Range(Cells(j, "A"), Cells(i, "A"))
            Cells(j, "C") = WorksheetFunction.Sum(.Offset(, 2))
            .Offset(, 2).Merge
            .Offset(, 1).Merge
            .Merge
        End With
    End If

Next
'Range("A:C").VerticalAlignment = xlCenter

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
@Akuini
Nice to meet you too :)

It's work!

Thank you very much , i'm just learning VBA and in the future I will try to add my own codes.
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Script to merge cells based on cell value
Replies
1
Views
84
Joe4
Joe4
J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
140
alansidman
A
H
  • Solved
Merging Duplicate Rows and time entries
Replies
5
Views
291
alansidman
A
A
  • Solved
Sum of Row with Letters and Merged Numbers in Different Cells
Replies
3
Views
250
Joe4
Joe4
F
  • Question Question
Merging/combining dup data in rows and Summing data in columns
Replies
6
Views
252
Fuji
F

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top