getting rid of multiple entries of the same name but adding a value together

[Ivn68]

in columnA I have a name and in columnB I have a value
there would be multiple entries for the same name but different values in columnB
i need to get rid of the duplicate entry but the values in columnB to be added together

thinkng im gonna need to run a loop in vba

 

[Ivn68]

It's adding it up and putting the result in column B and deleting the last name. I need the results to be in column C and retain the last name

 

[Fluff]

At the moment its outputting 2 sets of results, the unique values from col A & the count from col C. Where do you want those to be output?

 

[Ivn68]

Column A is first name column B is last name column C is a value. I need it get rid of duplicates but add column C together. I need column A and column B to stay the same.

So the data would look like this:

Steve Smith 2
Steve Smith 4
John Jones 5
John Jones 1

And then look like this:

Steve Smith 6
John Jones 6

 

[Fluff]

Do you only want to add the totals if both col A & B match?
At the moment it's only looking in col A

 

[Ivn68]

If column A match then column B will match for sure

 

[Ivn68]

Actually I need them both to match cause of common first names

 

[Fluff]

Ok, try

Sub Totalunique()
   Dim v As String
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         v = Cl.Value & "|" & Cl.Offset(, 1).Value
         If Not .exists(v) Then
            .Add v, Cl.Offset(, 2).Value
         Else
            .Item(v) = .Item(v) + Cl.Offset(, 2).Value
         End If
      Next Cl
      Range("A1").CurrentRegion.Offset(1).ClearContents
      Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
      Range("C2").Resize(.Count).Value = Application.Transpose(.items)
      Range("A2").Resize(.Count).TextToColumns Range("A2"), xlDelimited, , , False, False, False, False, True, "|"
   End With
End Sub

 

[Ivn68]

they added more colums now!fml!

so now i have column A,B,C,D,E,F

add totals of column F if column A,B,C, are the same column D and E dont have to be the same




can you tell me how to change the code if they add more columns in the future?

what if i just need one column to match like in column C to match?

 

[Fluff]

To add extra columns to the match you do it like

v = Cl.Value & "|" & Cl.Offset(, 1).Value [COLOR=#0000ff]& "|" & Cl.Offset(, 2).Value[/COLOR]

and to change the column being totalled

If Not .exists(v) Then
            .Add v, Cl.Offset(, [COLOR=#ff0000]5[/COLOR]).Value
         Else
            .Item(v) = .Item(v) + Cl.Offset(, [COLOR=#ff0000]5[/COLOR]).Value
         End If

You'll also need to change this line

Range("C2").Resize(.Count).Value = Application.Transpose(.items)

to column D

 

[Deepakchoudhary]

In my suggestion, you can use to filter unique values (at another place in similar table format 'C D') of column A by using "REMOVE DUPLICATE" and after you can apply SUMIFS formula to solve you purpose.

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]a[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]a[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD][/TD]
[TD="colspan: 2"]=SUMIFS($B$2:$B$15,$A$2:$A$15,E2)[/TD]
[/TR]
[TR]
[TD="class: xl66"]b[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]b[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]c[/TD]
[TD="class: xl66, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]c[/TD]
[TD="class: xl66, align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]d[/TD]
[TD="class: xl66, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]d[/TD]
[TD="class: xl66, align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]e[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]e[/TD]
[TD="class: xl66, align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]f[/TD]
[TD="class: xl66, align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]f[/TD]
[TD="class: xl66, align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]g[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]g[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]h[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]h[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]i[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]i[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]b[/TD]
[TD="class: xl66, align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]c[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]d[/TD]
[TD="class: xl66, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]e[/TD]
[TD="class: xl66, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]f[/TD]
[TD="class: xl66, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]