Solved

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

Posted on 2014-10-09
6
1,245 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now