CODEWITHSUNDEEP
excelexcel-formulavba
Tallboy
Tallboy

Reputation: 13457

Excel: How to prune duplicates and combine their values?

Say I have a simple two column table

value-a | 3
value-b | 7
value-b | 2
value-c | 5

i want the output to be

value-a | 3
value-b | 9
value-c | 5

Thanks

PS. I'm hoping this is just a really simple function. I attempted to use 'consolidate' but failed miserably. I'm not too familiar with Excel scripting or how to even run it.

Upvotes: 0

Views: 151

Answers (2)

pnuts
pnuts

Reputation: 59485

Might be worth considering Subtotal:

SO18903348 example

Upvotes: 0

user2140173
user2140173

Reputation:

There are two ways I can think of besides the tutorial link provided by Jaycal.

Non VBA Solution

Select entire Column A » right-click » Copy CTRL+C

Select cell C1 » right-click » Paste As Values

Select the entire Column C » go to Data Tab » Remove Duplicates

enter image description here

Once you click the Remove Duplicates on the next pop-up Continue with the current selection and make sure you only selected Column C

This will remove duplicates. Now all you need is to stick a formula in cell D1

=SUMIF(A:A,C1,B:B)

grab the right-bottom corner of the cell and drag it down til the last cell in Column C

This will solve your problem without VBA

enter image description here

VBA Solution

Sub RemoveDupsAndSumUp()
Application.ScreenUpdating = False
    Columns("A:A").Copy
    Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("$C:$C").RemoveDuplicates Columns:=1, Header:=xlNo
    Range("D1").FormulaR1C1 = "=SUMIF(C[-3],RC[-1],C[-2])"
    Range("D1").AutoFill Destination:=Range("D1:D" & Range("C" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions