Merging duplicate rows in Excel 2003

Question

I have a spreadsheet with two columns in the format:

title A   300
title B   345
title A   25
title C   105

I would like to merge the rows with duplicate titles while totaling the numeric value so the example above would become:

title A   325
title B   345
title C   105

I am using Excel 2003.

Answer 1

I think this one warrants a quick macro, give this a try (on a copy of your data in case of problems! I have tested this on Excel 2003 here, and it works for me, but as always it's best to be cautious!).

First, it will select the entire sheet you currently have active and sort by the A column. Then it will then look down the entire A column for things than match (100% match, this is case sensitive as well) and will add up thier values in the B column and remove the duplicate rows. Data in the duplicate rows in columns other than B will be lost.

I've added a couple of NOTE comments in the code with hints on the bits that are easiest to tweak.

Sub SortAndMerge()
    'Sort first
    'NOTE: Change this select if you wish the sort to be more precise
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    'And then merge
    Range("A1").Select

    'Keep going until we run out of entires in the first column
    Do While ActiveCell.Value <> 0

        'Loop while the row below matches
        Do While ActiveCell.Offset(1, 0).Value = ActiveCell.Value
            'The value on this row += the value on the next row
            'NOTE: Changing the 1 in the second places on *all three* of these
            '      offsets will change the row being merged (A+1=B, A+2=C, etc)
            ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value _
                                            + ActiveCell.Offset(1, 1).Value

            'Delete the duplicate row
            ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
            Selection.Delete Shift:=xlUp

            'Reselect the top row for this group
            ActiveCell.Offset(-1, 0).Select
        Loop

        'Step to next row
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Answer 2

Give your columns names (such as Title and Qty) and then create a Pivot Table with Title in the row labels and Qty in the Values (it should default to SUM).

This has the advantage that if you change your source data, you can refresh the pivot table and have it recalculate.