Click here to Skip to main content
14,975,234 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I was wondering on how I could accomplish this:

I have this Spreadsheet in excel and I want to combine two of the same value in a cell. This is using VBA.
Item | Description | Quantity
ABC  | Item 1      | 2
ABC  | Item 1      | 4
DEF  | Item 2      | 4

Sample output:
Item | Description | Quantity
ABC  | Item 1      | 6
DEF  | Item 2      | 4

*I want to add the quantity but still keep the value in item and description.
Updated 5-Mar-14 18:18pm

sorry, had to delete/repost - I couldn't get your exact result

this is using data -> subtotal

Item	Desc	Qty
ABC	Item 1	2
ABC	Item 1	4
ABC Total		6
DEF	Item 2	7
DEF Total		7
Grand Total	13

And this is using a filter on the Item column, then selecting level '2' in the left margin

Item	Desc	Qty
ABC Total		6
DEF Total		7
Grand Total	13

Without using scripting that's as close as I could get
I have already solved my own problem! Here is my code on how to achieve this output.

Sub DataChecker()
    Dim LR As Long, i As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    'Sorts Data First
    Range("A2:IV" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
    'For the next process, it deletes the data that are the same. Since it is arranged, all the duplicates will be together.
    For i = LR To 2 Step -1
            If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
                Cells(i - 1, "C").Value = Cells(i - 1, "C").Value + Cells(i, "C").Value
                Cells(i - 1, "C").Interior.Color = RGB(255, 0, 0)
            End If
    Next i
End Sub

Once done, column C will have cells that are highlighted in red just to let you know that they are total values of the duplicates.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900