VBA to merge duplicate rows and sum values in column

[sotplugy]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Room[/TD]
[TD]Quantity[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Small -- 2101[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]2[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Small -- 2102[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]2[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Medium -- 3101[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]2[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Medium -- 3102[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]2[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Small -- 2104[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]2[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]2[/TD]
[TD]Pear[/TD]
[/TR]
</tbody>[/TABLE]


Output:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Room[/TD]
[TD]Quantity[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Small -- 2101, 2102, 2104[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]3[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]3[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Small[/TD]
[TD]6[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD]3[/TD]
[TD]Total (Small)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Medium -- 3101, 3102[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]2[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]2[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]4[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD]2[/TD]
[TD]Total (Medium)[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[sotplugy]

*output for Orange under Small should sum to 4 instead of 3