Solved

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

Posted on 2014-10-09
6
1,507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

710 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