Solved

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

Posted on 2014-10-09
6
1,393 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

860 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