Macro Help - Group Data using 2 Values

I

itsmekarak

New Member
Joined
Sep 29, 2014
Messages
30
Hello,

One of my favorite go to macros is one I call "Group then Total" because it sorts the data, groups it based on the values in a particular column (column D in this example) and then totals that group of data. I use this macro to group merchant deposits by date. Here it is:

Sub Group_Then_Total()
Range("A1:I1212").Select
Selection.RowHeight = 15
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("D2") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:I1212")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dim MyData As Range, LastCell As Range
Dim lngRow As Long
Dim lngCol As Long
lngRow = Cells(Rows.Count, 1).End(xlUp).Row
lngCol = Cells(1, 1256).Columns.End(xlToLeft).Column
Set MyData = Range("d2").Resize(lngRow, lngCol)
With MyData
.Subtotal Groupby:=1, Function:=xlSum, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End Sub

QUESTION: Is there a way I can add a second column of data to determine what to group and total?

Here's my sample data:

[TABLE="width: 533"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]TRANS_CNT[/TD]
[TD="align: center"]PROCESS DATE[/TD]
[TD="align: center"]BATCH #[/TD]
[TD="align: center"]BATCH DATE[/TD]
[TD="align: right"] SETTLED $ [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: right"]100.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: right"]1154.1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]954.23[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12347[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]721.59[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]1221.65[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]121.54[/TD]
[/TR]
</tbody>[/TABLE]

After running the macro it looks like this (grouped by column D):


[TABLE="width: 508"]
<tbody>[TR]
[TD="align: center"]TRANS_CNT[/TD]
[TD="align: center"]PROCESS DATE[/TD]
[TD="align: center"]BATCH #[/TD]
[TD="align: right"]BATCH DATE[/TD]
[TD="align: right"] SETTLED $[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: center"]12345[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 100.00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 1,154.10[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]06/01/15 Total[/TD]
[TD="align: right"] 1,254.10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 954.23[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12347[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 721.59[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 1,221.65[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: center"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 121.54[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]06/02/15 Total[/TD]
[TD="align: right"] 3,019.01[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]Grand Total[/TD]
[TD="align: right"] 4,273.11[/TD]
[/TR]
</tbody>[/TABLE]


I want it to look like is this (Grouped by column D first then column C second):

[TABLE="width: 508"]
<tbody>[TR]
[TD="align: right"]TRANS_CNT[/TD]
[TD="align: right"]PROCESS DATE[/TD]
[TD="align: right"]BATCH #[/TD]
[TD="align: right"]BATCH DATE[/TD]
[TD="align: right"] SETTLED $ [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: right"]12345[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 100.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12345 Total[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 100.00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]06/01/15[/TD]
[TD="align: right"]12346[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 1,154.10[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12346 Total[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 1,154.10[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]06/01/15 Total[/TD]
[TD="align: right"] 1,254.10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 954.23[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 1,221.65[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]12346[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 121.54[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12346 Total[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"] 3,019.01[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]06/02/15[/TD]
[TD="align: right"]12347[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 721.59[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12347 Total[/TD]
[TD="align: right"]06/02/15[/TD]
[TD="align: right"] 721.59[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/02/15 Total[/TD]
[TD="align: right"] 3,019.01[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Grand Total[/TD]
[TD="align: right"] 4,273.11[/TD]
[/TR]
</tbody>[/TABLE]


Any help offered is appreciated!
 
You must log in or register to reply here.

Similar threads

M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
232
Madam_Xavier
M
H
  • Solved
Getting the last price for each duplicates ID
Replies
2
Views
180
Hasson
H
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
374
Fuji
F
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
268
Omar M
O
A
  • Solved
How sort duplicates data under each other of them for each duplicates dates
Replies
5
Views
187
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,833
Messages
6,193,212
Members
453,780
Latest member
Nguyentam2007

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top