can I sort by product number and merge the totals if they are the

[Eben]

I have a speadsheet with part numbers in column A. Some are duplicated up to
4 times and I want to merge them to get a total. It is 5000 lines so I dont
want to do it manually.

Thanks

 

[jlclyde]

I have a speadsheet with part numbers in column A. Some are duplicated upto
4 times and I want to merge them to get a total. It is 5000 lines so I dont
want to do it manually.

Thanks

You will need to load the code below into your VB window. To do this
you will need to hit Alt + F11. this will bring up VBA. Click on
View/ Project Explorrer. Find the name of your worksheet in the
list. Single click on worksheet. Go to insert and select Module.
Put this code in the module. Save your workbook. Run the macro by
going back to the excel sheet and selecting Tool/Macro/ RemoveDupes.
this will get rid of all duplicates in A and add up all of the Bs if
the As are the same. You can add more columns you woudl like to sum
by adding lines of code similar to code I have marked with here. you
will need to change how far it offsets. I hope this helps,
Jay


Sub RemoveDupes()

Dim Target As Range
Set Target = ActiveSheet.Range("A65536").End(xlUp)

Do Until Target.Row = 1
If Target = Target.Offset(-1, 0) Then
'this line will add the next column to the right together
Target.Offset(0, 1) = Target.Offset(0, 1) + Target.Offset(-1,
1)'Here
Target.Offset(-1, 0).EntireRow.Delete
Else
Set Target = Target.Offset(-1, 0)
End If
Loop
End Sub

 

[Shane Devenshire]

Hi,

I'm not exactly sure what you mean by merge them, but if you mean sum one of
the columns then you don't need a macro

Suppose your part numbers are in column A starting at A2 and the number you
want to sum is in B2:B100 then in C2 enter the following formula and copy it
down

=IF(COUNTIF(A$1:A2,A2)=1,SUMIF($A$2:$A$100,A2,$B$2:$B$100),"")

Now convert this column to values and sort on it. Delete the extra rows.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire