Combine two rows and add values while remove one of the rows

[sin2190]

Hello my name is Alex and I am a manager for a few cell phone stores and I need some help.
So basically I have two separate rows in an excel file containing a rebate reimbursement for a single phone number but my POS is currently unable to match the two numbers and reimbursement amount together. This is what I'd like to do as easy as possible.

-Match duplicates in a row (Phone numbers) Ex:Column A
-add the two value together (Rebate Amount) Ex: Column C
-Delete 2nd row after I get the sum

Normally it wouldn't be an issue for me because I used to do this manually but they just promoted me to District Manager and now I have multiple store to take care of and doing it manually is really time consuming.

In case I wasn't clear I attached an example. This is a very small version of what I am dealing with.


Anyone that could help, would be doing me a great service.


PS. File must remain in the same format, I don't think pivot table will actually assist

Just for the sake clarification. I will update the Excel file to show what I have and what I'm trying to achieve.

[southward]

Hi,

I recommend using a pivot table. It won't alter your data if you need it later.
Also recommend caution when using "Format as Table". It appeared that you had your rows of data selected when you used "Format as Table". It made the file cumbersome to open.

See attached file that has a pivot table of your data.


Oh! and Congratulations on the job promotion.

[sin2190]

Alright so allow me to reiterate, the information on the excel file has to be adjusted.
Basically this isn't for my information per-say but more for the POS system to be able to cross match this information with our expected reimbursements.
So, for one, Col. A (Phone Numbers) can not have duplicates so one of the two rows have to be deleted.
and two, the values(Col. C) of the rows with duplicates in Col A have to add up.

Thank you for your attempt and also for the congrats.

[sin2190]

Feel free to view the updated excel file.

[southward]

How about this?

I can't take credit for it. Borrowed it from Andrew Poulsom on: http://www.mrexcel.com/forum/excel-q...cate-rows.html
Modified it to 3 columns.

Sub Combine()
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim Rng As Range
    Set Sh = ActiveSheet
    Sh.Columns(4).Insert
    LastRow = Sh.Range("A65536").End(xlUp).Row
    With Sh.Range("A1:A" & LastRow).Offset(0, 3)
        .FormulaR1C1 = "=IF(COUNTIF(R1C[-3]:RC[-3],RC[-3])>1,"""",SUMIF(R1C[-3]:R[" & LastRow & "]C[-3],RC[-3],R1C[-1]:R[" & LastRow & "]C[-1]))"
        .Value = .Value
    End With
    Sh.Columns(3).Delete
    Sh.Rows(1).Insert
    Set Rng = Sh.Range("c1:c" & LastRow + 1)
    With Rng
        .AutoFilter Field:=1, Criteria1:="="
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub

The code is in the attached spreadsheet.

[sin2190]

OMG! I think thats it, but really quickly... Would you be able to adjusted the following
17 Col.
Phone Number is Col.7 or G Cell
And Amount is Col. 15 or O Cell?
So I can try it out.
Please and thank you.

[sin2190]

Would you be able to please rewrite the code here but instead of inputting the Column letters can you put what they mean?
I'd really appreciate learning a little more.

[southward]

I have modified it to work for Columns G and O. It is in the attached spreadsheet.

As stated earlier I borrowed the original macro from Andrew Poulsom on: http://www.mrexcel.com/forum/excel-q...cate-rows.html

When it come to excel and macro's I know enough to be dangerous. I don't know enough to even begin explaining how it works. Sorry

Sub Combine()
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim Rng As Range
    Range("o1").Copy Range("p1")
        Set Sh = ActiveSheet
    Sh.Columns(16).Insert
    LastRow = Sh.Range("g65536").End(xlUp).Row
    With Sh.Range("g1:g" & LastRow).Offset(0, 9)
        .FormulaR1C1 = "=IF(COUNTIF(R1C[-9]:RC[-9],RC[-9])>1,"""",SUMIF(R1C[-9]:R[" & LastRow & "]C[-9],RC[-9],R1C[-1]:R[" & LastRow & "]C[-1]))"
        .Value = .Value
    End With
    Sh.Columns(15).Delete
    Sh.Rows(1).Insert
    Set Rng = Sh.Range("o1:o" & LastRow + 1)
    With Rng
        .AutoFilter Field:=1, Criteria1:="="
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    Range("p1").Cut Range("o1")
End Sub