Merge duplicate rows and sum column for each name

[pdjm12]

Hi

The data I'm trying to consolidate has this structure. I found pieces of code around the forum but i just can't make it work, is there a way using VBA or a formula I can do this?

Make this:

A	B
	John
1	apple
2	apple
1	orange
	Peter
1	Lemon
1	Orange
2	Lemon
3	Orange
	Richard
1	orange
1	lemon
2	apple
1	apple
	James
1	lemon
1	coconut
2	apple
2	coconut
1	lemon

<tbody>
</tbody>

...

become this:

A	B
	John
3	apple
1	orange
	Peter
3	Lemon
4	Orange
	Richard
1	orange
1	lemon
3	apple
	James
2	lemon
3	coconut
2	apple

<tbody>
</tbody>

Thank you

 

[MickG]

Try this:-
NB:- This code will replace the data in columns "A & B".

[COLOR="Navy"]Sub[/COLOR] MG22Jun18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, Ray(), c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeConstants)
ReDim Ray(1 To Lst, 1 To 2)

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dn
        [COLOR="Navy"]If[/COLOR] Not .Exists(R.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
            .Add R.Offset(, 1).Value, R.Value
        [COLOR="Navy"]Else[/COLOR]
            .Item(R.Offset(, 1).Value) = .Item(R.Offset(, 1).Value) + R.Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
    
    c = c + 1
    Ray(c, 2) = Dn(1).Offset(-1, 1)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
            c = c + 1
            Ray(c, 2) = k: Ray(c, 1) = .Item(k)
        [COLOR="Navy"]Next[/COLOR] k
   .RemoveAll
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Range("A:B").ClearContents
Range("A1").Resize(c, 2).Value = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pdjm12]

Thank you so much! Your code works great

 

[MickG]

You're welcome