how to automatically sum and merge rows based on same value in column?

[chezchez]

hello, can anyone help me perform the following calculation in excel please?

I have a long list of items and their quantities, example:

101001	1
101001	6
101001	2
101001	1
101002	6
101002	1
101003	6
101004	6
101005	6
101050	1
101050	1
101050	5
101050	1
101050	2
101050	2
101050	1

<tbody>
</tbody>

now i would like to merge all the rows with the same value in column A and add up the corresponding values in column B to return the following:

101001	10
101002	7
101003	6
101004	6
101005	6
101050	13

<tbody>
</tbody>

I am guessing I have to run a macro, but I have no idea how to do that... any help would be much appreciated!
thanks, chez

 

[jdunnam]

Sorry tried to post image didnt work. Data Headers Start at B4

									Quote #	13534
FINISHED
B	C	D	E	F	G	H	I	J	K	L	M
Code	GAP	Wages	Rate	Billed	Est	Cost	n	Rate	cost	Fee	Fee2
CA 8810	$ 175,000	$ 175,000	2.58%	$ 2,375	$ 1,540	$ 308	0.00%	1.20%	$ 1,200	3.78%	$ 3,575
CA 9008	$ 960,000	$ 960,000	49.04%	$ 194,452	$ 160,416	$ 32,083	0.00%	1.20%	$ 3,000	50.24%	$ 197,452
CO 9014	$ 795,000	$ 795,000	6.60%	$ 24,899	$ 28,382	$ 5,676	0.00%	1.20%	$ 1,800	7.80%	$ 26,699
Totals	$ 1,930,000	$ 1,930,000		$ 221,726	$ 190,338	$ 38,068			$ 6,000		$ 227,726

<tbody>
</tbody>

----------------------------------------------------------------------------------------------------------------------------------
ORIGINAL

CA 9008	$ 250,000	$ 250,000	33.42%	$ 83,550	$ 41,775	$ 8,355	0.00%	1.20%	$ 3,000	34.62%	$ 86,550
CA 8810	$ 100,000	$ 100,000	1.76%	$ 1,760	$ 880	$ 176	0.00%	1.20%	$ 1,200	2.96%	$ 2,960
CO 9014	$ 150,000	$ 150,000	3.57%	$ 5,355	$ 5,355	$ 1,071	0.00%	1.20%	$ 1,800	4.77%	$ 7,155
CA 9008	$ 710,000	$ 710,000	15.62%	$ 110,902	$ 118,641	$ 23,728	0.00%	0.00%	$ -	15.62%	$ 110,902
CA 8810	$ 75,000	$ 75,000	0.82%	$ 615	$ 660	$ 132	0.00%	0.00%	$ -	0.82%	$ 615
CO 9014	$ 645,000	$ 645,000	3.03%	$ 19,544	$ 23,027	$ 4,605	0.00%	0.00%	$ -	3.03%	$ 19,544

<tbody>
</tbody>

 

[MickG]

Try this:-
Nb:- Your Dollar formatted numbers appear on my sheet as text, so I removed the dollars signs and reformatted as dollars.

[COLOR="Navy"]Sub[/COLOR] MG15Apr38
[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] lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B4"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 1 To 11
           .Item(Dn.Value).Offset(, n).Value = _
           .Item(Dn.Value).Offset(, n).Value + Dn.Offset(, n).Value
        [COLOR="Navy"]Next[/COLOR] n
           [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
  
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
lst = Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & lst + 1).Value = "Totals"
    col = Array(1, 2, 4, 5, 6, 9, 11)
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(col)
        Range("B" & lst + 1).Offset(, col(n)) = Application.Sum(Range("B4").Offset(, col(n)).Resize(lst - 3))
        Range("B" & lst + 1).Offset(, col(n)).NumberFormat = "[$$-409]#,##0"
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jdunnam]

It worked..Thank you so much for your help.

 

[MickG]

You're welcome