Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can I sort by product number and merge the totals if they are the
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
can I sort by product number and merge the totals if they are the
On Jan 6, 1:19*pm, Eben wrote:
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
can I sort by product number and merge the totals if they are the
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 "Eben" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum totals based on product name | Excel Worksheet Functions | |||
cumulitive Totals and sort | Excel Worksheet Functions | |||
Totals of each product in a new worksheet | Excel Worksheet Functions | |||
Sort data into order of product | Excel Discussion (Misc queries) | |||
Sort data into order of product | Excel Worksheet Functions |