VBA for grouping lines with same ID

[yxz152830]

Hello Gurus,
could you give me any suggestions on how to write VBA script to group lines with same ID(they are different in values) for further scenario breakdowns? A simplified table is shown below. Thanks!

ID	Value
A	1
B	3
B	1
C	10
C	1
C	2

 

[Coyotex3]

What kind of result are you expecting?

For ID C, how should this be grouped? Should one column have C and the other column have 10-1-2? Or should it be 10,1,2 or 1012 etc? How should the values be grouped? Or maybe you are hoping to sum these?

 

[Peter_SSs]

to group lines with same ID

Grouping lines can be interpreted a few different ways in Excel so I'm not sure just what you are after

1. They are already "grouped" in that all the "A" values are together, all the "B" values are together etc.

2. Here is another way they can be "grouped"

3. A third way using Subtotal

This is code to produce #3 above, starting with, for example, the sample data below

Sub Group_By_SubTotal()
  With Range("A1").CurrentRegion
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), Replace:=True, SummaryBelowData:=True
  End With
End Sub

yxz152830.xlsm
	A	B
1	ID	Value
2	B	1
3	A	1
4	C	10
5	B	3
6	C	1
7	C	2
Sheet1

 

[Coyotex3]

@yxz152830 I don’t believe I solved your question yet.

@Peter_SSs gave you some options as well.

You have not told us how you’d like for these to be grouped.

 

[Peter_SSs]

@yxz152830 I don’t believe I solved your question yet.

I agree, so I have removed the solution mark from post 2.