Solved

Excel VBA, code to group by a column and sum other columns

Posted on 2014-10-09
6
1,343 Views
Last Modified: 2014-10-10
I have a list of data in Excel.  I would like to group by column A and sum columns F,G,H,I, and J.  I'd like to create some group by code like you can do in SQL.
0
Comment
Question by:jnikodym
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40371075
Sounds like you could use Excel's subtotalling function to do this, but even better would be using a PivotTable (no VBA required).

Does this example workbook seem to provide something you're looking for?
EE-PivotExample.xlsx
0
 
LVL 6

Expert Comment

by:johnb25
ID: 40371081
Have you considered using the Data/Subtotal option within Excel, or Pivot Tables?
0
 

Author Comment

by:jnikodym
ID: 40371222
I don't want to use the Data/Subtotal option in Excel since i don't like the way it works and indents things.  I realize i can also do a pivot table, which i will probably do, but i would also like to see my data sheet grouped by a column.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40371325
I'm in agreement with you on Subtotals; they're finicky and not really good to introduce into a dataset.  It's better to report off that set (like a PivotTable) than modify it.

That said, what is it you want to do with your data that a PivotTable cannot do?  You could just sort the data by the values in column A, but if you don't want the addition of subtotaled lines, where do you want to report these total amounts?

If you don't like PivotTables - or you have another specific report sheet, then I recommend using GETPIVOTDATA functions to retrieve summary data or SUMIF, COUNTIF, SUMIFS, or COUNTIFS functions to report summary data.

See this modified example workbook showing these methods for reporting.

-Glenn
EE-PivotExample.xlsx
0
 

Author Comment

by:jnikodym
ID: 40371709
I'm just going to use some code to pull the unique values in the column and then use SUMIFS formulas to get my totals.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40371764
Okay, if you would like VBA code to return the unique values in column A, this should work for you.  I've written this code to insert the unique values in a column starting at the selected cell.  As a precaution, if there is any data in the cell, it will not run; you'd want to delete any content first if overwriting.

Option Explicit
Sub Insert_Unique_Values()
    Dim Dic As Object
    Dim rng As Variant
    Dim x As Long
    Dim arrUList As Variant
    
    If ActiveCell.Value <> "" Then Exit Sub
    
    'change the Sheet name below ("Data") to match yours
    rng = Sheets("Data").Range("A2", Sheets("Data").Range("A2").End(xlDown))
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.compareMode = vbTextCompare
    
    For x = 1 To UBound(rng, 1)
        Dic.Item(rng(x, 1)) = rng(x, 1)
    Next x
    'assign Dic to array then insert in cells
    arrUList = Dic.items
    For x = 0 To UBound(arrUList)
        ActiveCell.Offset(x, 0).Value = arrUList(x)
    Next x
End Sub

Open in new window


-Glenn
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question