Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Auto Grouping
Hi,
I want Excel (through a click of a button - in Sheet2) to scan through the entire list in Sheet1, then based on the list in Sheet1, generate a list of unique group and finally sum the total line for each group. Sheet1: A B C 1 desc type name 2 dell pc pc1 3 hp ws ws2 4 sun ws ws1 5 hp printer prt1 Sheet2: A B 1 type count 2 pc 1 3 ws 2 4 printer 1 Can anyone help? Thank you, choo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Auto Grouping
i HAVE A SIMILAR PROBLEM - DID YOU SOLVE THIS ISSUE?
-- Thanks Sally "choo" wrote: Hi, I want Excel (through a click of a button - in Sheet2) to scan through the entire list in Sheet1, then based on the list in Sheet1, generate a list of unique group and finally sum the total line for each group. Sheet1: A B C 1 desc type name 2 dell pc pc1 3 hp ws ws2 4 sun ws ws1 5 hp printer prt1 Sheet2: A B 1 type count 2 pc 1 3 ws 2 4 printer 1 Can anyone help? Thank you, choo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Auto Grouping
Hi Choo, Sally,
First of all, you need VBA to select what you want to get grouped. Let's assume, that you are in the leftmost top cell of the range you are interested in (in your example it would be A1). Then you run the following code: Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select After that you can do the grouping. This code does it for you: Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(3), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 The 2 means column 2 in your range contains which element should be counted, the Array(3) says that the result of each counting should be displayed in column 3 of your range. The last row says that it should do the grouping and only display the results and hide the elements. Hope this is what you have expected. Have a nice weekend Udo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto grouping. | Excel Discussion (Misc queries) | |||
Using Grouping and Sorting and Auto Filter | Excel Discussion (Misc queries) | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Auto Excel workbook close: save= false during an auto subroutine | Excel Programming | |||
Auto Filter and Grouping | Excel Discussion (Misc queries) |