VBA to merge duplicate rows and sum values in column

sotplugy

New Member
Joined
Aug 16, 2016
Messages
3
RE: https://www.mrexcel.com/forum/excel...duplicate-rows-sum-values-certain-column.html

Tried the code here but doesn't seem to work well for my case.


Example:
RoomQuantityDescription
Small -- 2101
Small1Apple
Small1Orange
Small2Pear
Small -- 2102
Small1Apple
Small1Orange
Small2Pear
Medium -- 3101
Medium1Apple
Medium1Orange
Medium2Pear
Medium -- 3102
Medium1Apple
Medium1Orange
Medium2Pear
Small -- 2104
Small1Apple
Small2Orange
Small2Pear

<tbody>
</tbody>


Output:

RoomQuantityDescription
Small -- 2101, 2102, 2104
Small3Apple
Small3Orange
Small6Pear
Count3Total (Small)
Medium -- 3101, 3102
Medium2Apple
Medium2Orange
Medium4Pear
Count2Total (Medium)

<tbody>
</tbody>

Currently have this:


Dim Rng As range, Dn As range, n As Long, nRng As range
Set Rng = range(range("C2"), range("E" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Dn
Else
If nRng Is Nothing Then Set nRng = _
Dn Else Set nRng = Union(nRng, Dn)
.Item(Dn.Value).Offset(, -1) = .Item(Dn.Value).Offset(, -1) + Dn.Offset(, -1)
End If
Next
If Not nRng Is Nothing Then nRng.EntireRow.Delete
End With


Any ideas how to allow for a sandwiched group (medium) in the groups of Small?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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