I need macro that adds the values without duplicates

[1234]

Hi,

I have in column J some codes and in column R some numeric values. In
J data has duplicates. I want a macro that adds all the values of each
code but without duplicates. Something like this:

J R
PL 32 10
PL 22 5
PL 32 3

It must show in other column the sum of each code. Example PL 32 =
13 y PL 22=5

How can I do this?

Thanks!!

 

[Bernard Liengme]

Why not make a Pivot Table?

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[1234]

Thanks for your answer Bernnard. I know it can be done with pivot
tables and subtotals but it crashes my sheet and I need something ore
automatic. With a pivot table I have to delete it after or create in
an additional sheet and I don´t want that.

Thanks for your time anyway!!!

 

[Bernard Liengme]

Here is a subrountiine that does what you want

Sub LikePivot()
'Adjust the top 5 statements to suit your needs
'Note the last bit of code sends output the SecondSheet starting in A1
FirstRow = "1"
FirstCol = "J"
DataCol = "R"
FirstSheet = "Sheet1"
SecondSheet = "Sheet2"


Dim myTable(1000, 2)
Worksheets(FirstSheet).Activate
FirstCell = FirstCol & FirstRow
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Set MyRange = Range(FirstCell & ":" & FirstCol & Cells(Rows.Count,
FirstCol).End(xlUp).Row)
TableCount = 1

For j = FirstRow To LastRow
MyCode = Cells(j, FirstCol).Value
MyData = Cells(j, DataCol).Value

If TableCount = 1 Then
myTable(1, 1) = MyCode
myTable(1, 2) = MyData
FoundFlag = True
TableCount = TableCount + 1
Else
FoundFlag = False
For k = 1 To TableCount
If myTable(k, 1) = MyCode Then
myTable(k, 2) = myTable(k, 2) + MyData
FoundFlag = True
Exit For
End If
Next k
End If

If FoundFlag = False Then
myTable(TableCount, 1) = MyCode
myTable(TableCount, 2) = MyData
Debug.Print MyCode & " add to table at " & TableCount
Debug.Print myTable(TableCount, 1) & "---" & myTable(TableCount, 2)
FoundFlag = False
TableCount = TableCount + 1
End If
Next j


TableCount = TableCount - 1
Worksheets(SecondSheet).Activate
For j = 1 To TableCount
Cells(j, "A") = myTable(j, 1)
Cells(j, "B") = myTable(j, 2)
Next j
End Sub


best wishes

 

[keiji kounoike]

Try this one. The macro below put the sum of each code from column T.

Sub Total_by_code()
Const CodeCol = "J" 'Column where your Code reside
Const ValueCol = "R" 'Column where your Value reside
Const StotalCol = "T" 'Column where sum of each code are put in from
Dim CodeRng As Range, ValueRng As Range, StotalRng As Range
Dim StotalCell As Range, NextCell As Range, rng As Range
Dim startrow As Long, lastrow As Long
Dim Col As Variant, ncol As Long

startrow = 1 'first row's number where data reside
lastrow = Cells(Rows.Count, CodeCol).End(xlUp).Row
Set CodeRng = Range(Cells(startrow, CodeCol), Cells(lastrow, CodeCol))
Set ValueRng = Range(Cells(startrow, ValueCol), _
Cells(lastrow, ValueCol))
Set StotalRng = Range(Cells(startrow, StotalCol), _
Cells(startrow, Columns.Count))
Set StotalCell = Cells(startrow, StotalCol)

StotalRng.Resize(2).ClearContents

For Each rng In CodeRng
If StotalCell = "" Then
StotalCell = rng.Value
StotalCell.Offset(1, 0).Formula = "=sumif(" & _
CodeRng.Address(False, False) & ",""" & rng.Value & _
"""," & ValueRng.Address(False, False) & ")"
Set NextCell = StotalCell.Offset(0, 1)
Else
Col = Application.Match(rng.Value, StotalRng, 0)
If IsError(Col) Then Col = 0
If Col > 0 Then
StotalCell.Offset(0, Col - 1) = rng.Value
StotalCell.Offset(1, Col - 1).Formula = "=sumif(" & _
CodeRng.Address(False, False) & ",""" & rng.Value & _
"""," & ValueRng.Address(False, False) & ")"
Else
NextCell = rng.Value
NextCell.Offset(1, 0).Formula = "=sumif(" & _
CodeRng.Address(False, False) & ",""" & rng.Value & _
"""," & ValueRng.Address(False, False) & ")"
Set NextCell = NextCell.Offset(0, 1)
End If
End If
Next
End Sub

Keiji

 

[helene and gabor]

=sumif(J1:J50, "=pl32", R1:R50)
shall give the sum of all pl32 code numbers.

best regards,

Gabor Sebo
MA USA