Solved

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

Posted on 2014-10-09
6
1,298 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

920 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

15 Experts available now in Live!

Get 1:1 Help Now