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

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

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