0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A macro to group by column & sum values

The Microsoft Office Software comprises Microsoft Excel which is a spreadsheet application featuring graphic tables, calculations, a macro programming language, called VBA (Visual Basics for Applications) and pivot tables. A pivot table is used to list data; it recognizes and summarizes that data to obtain specified results. A macro represents a list of commands or actions to make lengthy data or repetitive tasks in Excel Office Software faster; this can be run whenever you need to perform the task. It takes the values, performs the required operation and returns the value accordingly. If you want to write a macro to group by column and sum value then just start the macro by using the relevant commands.


Issue


I need to write a macro for the following example:

Item Qty Length    
A 1 100    
A 1 100    
B 2 200    
B 1 100    
B 5 100    
C 4 200    
C 2 100    
C 1 200    
C 3 100 



Basically I want to group by Column A (i.e. Item) and Column C (i.e. length) and also want to add the total of each change in lengths, for an item. In this case, the result on the new sheet would be as below:

Item Qty Length    
A 2 100    
B 2 200    
B 6 100    
C 5 200    
C 5 100 


Hope this make sense.

Can anyone help me write an Excel macro for this please?

Solution


Try this
Assumption:
  • 1. When you start a macro, the sheet from where the data is to be copied from, is the active sheet
  • 2. An empty cell in column A indicates the end of the data
  • 3. You want to paste to sheet 3 (correct in macro if that is not the case)



Sub consolidateData()   

Dim lRow As Long   
Dim ItemRow1, ItemRow2 As String   
Dim lengthRow1, lengthRow2 As String   

    Columns("A:C").Select   
    Selection.Copy   
       
    Sheets("Sheet3").Select   
       
    Range("A1").Select   
    ActiveSheet.Paste   
       
    Cells.Select   
    Selection.Sort _   
        Key1:=Range("A2"), Order1:=xlAscending, _   
        Key2:=Range("C2"), Order2:=xlDescending, _   
        Header:=xlYes, OrderCustom:=1, _   
        MatchCase:=False, Orientation:=xlTopToBottom, _   
        DataOption1:=xlSortNormal   
           
    lRow = 2   
    Do While (Cells(lRow, 1) <> "")   
           
        ItemRow1 = Cells(lRow, "A")   
        ItemRow2 = Cells(lRow + 1, "A")   
           
        lengthRow1 = Cells(lRow, "C")   
        lengthRow2 = Cells(lRow + 1, "C")   
           
        If ((ItemRow1 = ItemRow2) And (lengthRow1 = lengthRow2)) Then   
            Cells(lRow, "B") = Cells(lRow, "B") + Cells(lRow + 1, "B")   
            Rows(lRow + 1).Delete   
       
        Else   
            lRow = lRow + 1   
        End If   
    Loop   
End Sub

Note


Thanks to rizvisa1 for this tip on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

Published by . Latest update on by aakai1056.

This document, titled "Excel - A macro to group by column & sum values," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).

0 Comments