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.

A	B			C	D
a	2			a	2		=SUMIFS($B$2:$B$15,$A$2:$A$15,E2)
b	4			b	5
c	6			c	11
d	8			d	14
e	10			e	17
f	12			f	20
g	5			g	5
h	5			h	5
i	9			i	9
b	1
c	5
d	6
e	7
f	8

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>