How to merge duplicate rows and sum value
Category: [Excel] Demo Available
How to merge duplicate rows and sum value
Requirement:
The user wants to know if it is possible for the linked macro to return each unique record from columns A to D and not just based on column B being unique.
The user has included an example / result below of what they are wanting the macro to do:
| Example: | ||||
| Delivery | Material | Description | SU | Delivery Qty |
| 8000001 | 12345C | 1234 Formula Forty | DR | 3 |
| 8000001 | 222222C | 2222 AIO Cleaner | CS | 12 |
| 8000004 | 222222C | 2222 AIO Cleaner | CS | 12 |
| 8000001 | 12345C | 1234 Formula Forty (2) | DR | 3 |
| 8000001 | 222222C | 2222 AIO Cleaner | CS (2) | 12 |
| 8000001 (2) | 222222C | 2222 AIO Cleaner | CS | 12 |
| 8000001 | 12345C | 1234 Formula Forty | DR | 5 |
| 8000001 | 222222C | 2222 AIO Cleaner | CS | 21 |
| 8000004 | 222222C | 2222 AIO Cleaner | CS | 9 |
| Result: | ||||
| Delivery | Material | Description | SU | Delivery Qty |
| 8000001 | 12345C | 1234 Formula Forty | DR | 8 |
| 8000001 | 222222C | 2222 AIO Cleaner | CS | 33 |
| 8000004 | 222222C | 2222 AIO Cleaner | CS | 21 |
| 8000001 | 12345C | 1234 Formula Forty (2) | DR | 3 |
| 8000001 | 222222C | 2222 AIO Cleaner | CS (2) | 12 |
| 8000001 (2) | 222222C | 2222 AIO Cleaner | CS | 12 |
Solution:
Try this for results on sheet2.
Code:
Sub nSum()
Dim Rng As Range, Dn As Range, n As Long, Txt As String, Ac As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 5)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 4))), ",")
If Not .Exists(Txt) Then
n = n + 1
For Ac = 1 To 5: ray(n, Ac) = Dn.Offset(, Ac - 1): Next Ac
.Add Txt, n
Else
ray(.Item(Txt), 5) = ray(.Item(Txt), 5) + Dn.Offset(, 4)
End If
Next
n = .Count
End With
With Sheets("Sheet2").Range("A1").Resize(n, 5)
.Value = ray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by MickG.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy a sheet and rename from a list, ignore duplicates |
| How to use IndexMatch formula that ignores duplicates |
| How to find sequence of a column with duplicates |
| How to compare two workbooks with multiple sheets and highlighting duplicates |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.