How to combine the value of a cell?

Question

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.
Ex.

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.

Posted 5-Mar-14 15:15pm

mitchiee1226

Updated 5-Mar-14 18:18pm

v4

Add a Solution

2 solutions

Solution 2

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

Posted 5-Mar-14 16:46pm

Garth J Lancaster

Updated 5-Mar-14 16:47pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

mitchiee1226 · Accepted Answer · 2014-03-05T20:19:00

I have already solved my own problem! Here is my code on how to achieve this output.

VB

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
                Rows(i).Delete
                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.