excel vba remove duplicates and sum corresponding values

Question

I found this code but i can't get it to work for my cell range. My example is below. "Corrected Order" is in col C, "Qty" is in col D. I want to remove the duplicates in col C, summing the corresponding values in col D and paste into range F1:G40. The code below works if I copy&paste col C to Col A, otherwise it removes duplicates but values are all "0"??

A   B   C   D
    Corrected Order (LCP)   Qty
    Orange  12
    Pear    9
    Pear    9
    Pear    6
    Orange  6
    Orange  3
    Orange  1
    Apple   34
    Apple   4
    Apple   4
    Apple   67



Option Explicit

Sub main()

With Worksheets("Fruit Stock") '<== change "Fruit Stock" as per your actual sheet name

    With .Range("C1:D40").Resize(.Cells(.Rows.Count, 3).End(xlUp).Row)
        .Copy
        With .Offset(, .Columns.Count + 1)
            .PasteSpecial xlPasteValues ' copy value and formats
            .Columns(2).Offset(1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = "=SUMIF(C1,RC1,C[-" & .Columns.Count + 1 & "])"
            .Value = .Value
            .RemoveDuplicates 1, xlYes
        End With
    End With

End With
End Sub

Do you want to remove the duplicate fruit names but add the values of all fruits. So one line for Pears showing 24 total? — Scott Holtzman
– Scott Holtzman, Commented Mar 9, 2018 at 16:29
No I have different data below and across from this range. I have reserved 2 columns for this consolidated data. I know I could use the consolidated function to do this but I want a quick command button macro as I will be doing this function regularly — Donnie Neylon
– Donnie Neylon, Commented Mar 9, 2018 at 16:36
Sorry just to reply to the first reply, yes that is what i want Pears 24 in a shifted column — Donnie Neylon
– Donnie Neylon, Commented Mar 9, 2018 at 16:41
Why not just use an if statement to make the SUMIF equation, where reference is blank or not found, to display ""? — Cyril
– Cyril, Commented Mar 9, 2018 at 16:41

glycoaddict · Accepted Answer · 2018-03-09 17:02:22Z

1

Make the correction to the "CX, RCX" where X is your column number (X=3 for Column C) Also corrected a few absolute addresses which meant that it only worked for the one case. Esp the Resizing to 3 columns didn't make sense.

Sub main()

With ActiveSheet '<== change "Fruit Stock" as per your actual sheet name

    With Range("C1:D40")
        Set r = .Resize(.Cells(.Rows.Count, .Columns.Count).End(xlUp).Row)

        With r

            .Copy
            With .Offset(, .Columns.Count + 1)
                .PasteSpecial xlPasteValues ' copy value and formats
                .Columns(2).Offset(1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = "=SUMIF(C" & r.Column & ",RC" & r.Column & ",C[-" & .Columns.Count + 1 & "])"
                .Value = .Value
                .RemoveDuplicates 1, xlYes
            End With

        End With

End With
End With
End Sub

edited Mar 9, 2018 at 17:02

answered Mar 9, 2018 at 16:41

glycoaddict

9698 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Donnie Neylon Over a year ago

The answer above has helped me but I have another problem. I need to replace duplicates removed with a nominal value for example "Duplicates". The reason I have 4 columns let's say, Fruit, Fruit Qty, Veg, Veg Qty. My vba code is consolidating Fruit and Fruit Qty to a designated 2 columns and Veg and Veg Qty to another designated 2 columns. The problem is that if the Fruit col has more duplicates than the Veg col the data gets shifted upwards to misalign. If I can replace the duplicates removed with "Duplicate I shouldn't have this problem. Any suggestions?

Donnie Neylon Over a year ago

So if Fruit col had 4 duplicates and Veg col has 1 duplicate the misalignment is 3 cells.

Collectives™ on Stack Overflow

excel vba remove duplicates and sum corresponding values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related