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:
<tbody>
</tbody>
Output:
<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?
Tried the code here but doesn't seem to work well for my case.
Example:
Room | Quantity | Description |
Small -- 2101 | ||
Small | 1 | Apple |
Small | 1 | Orange |
Small | 2 | Pear |
Small -- 2102 | ||
Small | 1 | Apple |
Small | 1 | Orange |
Small | 2 | Pear |
Medium -- 3101 | ||
Medium | 1 | Apple |
Medium | 1 | Orange |
Medium | 2 | Pear |
Medium -- 3102 | ||
Medium | 1 | Apple |
Medium | 1 | Orange |
Medium | 2 | Pear |
Small -- 2104 | ||
Small | 1 | Apple |
Small | 2 | Orange |
Small | 2 | Pear |
<tbody>
</tbody>
Output:
Room | Quantity | Description |
Small -- 2101, 2102, 2104 | ||
Small | 3 | Apple |
Small | 3 | Orange |
Small | 6 | Pear |
Count | 3 | Total (Small) |
Medium -- 3101, 3102 | ||
Medium | 2 | Apple |
Medium | 2 | Orange |
Medium | 4 | Pear |
Count | 2 | Total (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?