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

 

[JackDanIce]

Welcome to the board.

You can achieve this without a macro, you would use Data->Advanced to create a list of unique values and then a SUMIF function to total the values for each unique item

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG30Nov12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value).Value = .Item(Dn.Value).Value + Dn.Offset(, 1)
            [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] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[chezchez]

wow, this is amazing!!!! thanks so much!!! just saved me hours of work!!
the first option with the IFSUM worked a gem! didn't make it to the other one, but thanks to both of you!! much appreciated!!!

 

[JackDanIce]

You're welcome, thanks for the feedback

 

[jdunnam]

Is it possible to get this to not just do this for column B but loop until last column?

Try this:-

[COLOR=Navy]Sub[/COLOR] MG30Nov12
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] nRng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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.Offset(, 1)
    [COLOR=Navy]Else[/COLOR]
        .Item(Dn.Value).Value = .Item(Dn.Value).Value + Dn.Offset(, 1)
            [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] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Did you want the code to delete duplicates in individual columns , or duplicates within the whole range.???

 

[jdunnam]

Thanks for the quick response. I would like it to delete duplicates within the whole range, after summing like values in column B. Column Header always starts at B4 typically to M as last column but could change.

 

[MickG]

On reviewing my code I see it is intended to delete rows and amalgamate the duplicate offset data, because of the multiple columns now involved, I'm not totally sure what your Data and Results should look like.
Can you show a simple example of your data and expected results.

 

[jdunnam]

-- removed inline image ---

-- removed inline image ---