How to group data (rows) using month and year in date column using macro?

NPATEL

New Member
Joined
May 11, 2013
Messages
3
I want to group data (rows) using month and year in date column using macro.

For example.

Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125

I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Welcome to the MrExcel board!

Perhaps a Pivot Table with the Date column grouped by Month and Year as shown in columns F & G here.

Excel Workbook
ABCDEFG
1DateIDAmountRow LabelsSum of Amount
21/01/1312345$1002013
32/01/1313452$73Jan348
43/01/1312232$50Feb415
54/01/1334232$125Mar348
61/02/1312322$67Grand Total1111
72/02/1312345$100
83/02/1313452$73
94/02/1312232$50
105/02/1334232$125
111/03/1312345$100
122/03/1313452$73
133/03/1312232$50
144/03/1334232$125
15
PT



Another option would be to add an extra column (I've inserted a new column A) and populate it with a formula like this, then use Excel's built-in Subtotal feature. Subtotal automatically inserts the grouping feature.

A. After formula but before the Subtotal feature

Excel Workbook
ABCD
1mmyyyyDateIDAmount
22013011/01/1312345$100
32013012/01/1313452$73
42013013/01/1312232$50
52013014/01/1334232$125
62013021/02/1312322$67
72013022/02/1312345$100
82013023/02/1313452$73
92013024/02/1312232$50
102013025/02/1334232$125
112013031/03/1312345$100
122013032/03/1313452$73
132013033/03/1312232$50
142013034/03/1334232$125
15
Subtotal



After Subtotal.

Excel Workbook
ABCD
1mmyyyyDateIDAmount
22013011/01/1312345$100
32013012/01/1313452$73
42013013/01/1312232$50
52013014/01/1334232$125
6201301 Total$348
72013021/02/1312322$67
82013022/02/1312345$100
92013023/02/1313452$73
102013024/02/1312232$50
112013025/02/1334232$125
12201302 Total$415
132013031/03/1312345$100
142013032/03/1313452$73
152013033/03/1312232$50
162013034/03/1334232$125
17201303 Total$348
18
19Grand Total1111
Subtotal



If either of these suggestions looks useful but you need further help, provide more details, including which Excel version you are using.
 
Upvote 0
I want to group data (rows) using month and year in date column using macro.

For example.

Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125

I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
You asked for a macro.
Try this one. No extra columns needed.
Code:
Sub months()

Dim c As Range, a as Range

Set c = Range("A2")
Set a = c.Offset(, 2)

Do
If Evaluate("=month(" & c.Address & ")") <> Evaluate("=month(" & c(2).Address & ")") Then
    c(2).Resize(2, 3).Insert
    c(2).Offset(, 2) = "=sum(" & Range(a, c.Offset(, 2)).Address & ")"
    c(2).Offset(, 2).Font.Bold = 1
    Set c = c.End(4)
    Set a = c.Offset(, 2)
Else
    Set c = c(2)
End If
Loop Until c.End(4).Row = Rows.Count

c(2).Offset(, 2) = "=sum(" & Range(a, c.Offset(, 2)).Address & ")"
c(2).Offset(, 2).Font.Bold = 1

End Sub
 
Upvote 0
Welcome to the MrExcel board!

Perhaps a Pivot Table with the Date column grouped by Month and Year as shown in columns F & G here.

PT

*ABCDEFG
1DateIDAmount**Row LabelsSum of Amount
21/01/1312345$100**2013*
32/01/1313452$73**Jan348
43/01/1312232$50**Feb415
54/01/1334232$125**Mar348
61/02/1312322$67**Grand Total1111
72/02/1312345$100****
83/02/1313452$73****
94/02/1312232$50****
105/02/1334232$125****
111/03/1312345$100****
122/03/1313452$73****
133/03/1312232$50****
144/03/1334232$125****
15*******

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Another option would be to add an extra column (I've inserted a new column A) and populate it with a formula like this, then use Excel's built-in Subtotal feature. Subtotal automatically inserts the grouping feature.

A. After formula but before the Subtotal feature

Subtotal

*ABCD
1mmyyyyDateIDAmount
22013011/01/1312345$100
32013012/01/1313452$73
42013013/01/1312232$50
52013014/01/1334232$125
62013021/02/1312322$67
72013022/02/1312345$100
82013023/02/1313452$73
92013024/02/1312232$50
102013025/02/1334232$125
112013031/03/1312345$100
122013032/03/1313452$73
132013033/03/1312232$50
142013034/03/1334232$125
15****

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A2=TEXT(B2,"yyyymm")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


After Subtotal.

Subtotal

*ABCD
1mmyyyyDateIDAmount
22013011/01/1312345$100
32013012/01/1313452$73
42013013/01/1312232$50
52013014/01/1334232$125
6201301 Total**$348
72013021/02/1312322$67
82013022/02/1312345$100
92013023/02/1313452$73
102013024/02/1312232$50
112013025/02/1334232$125
12201302 Total**$415
132013031/03/1312345$100
142013032/03/1313452$73
152013033/03/1312232$50
162013034/03/1334232$125
17201303 Total**$348
18****
19Grand Total**1111

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


If either of these suggestions looks useful but you need further help, provide more details, including which Excel version you are using.

Thanks Peter for your quick reply.

I am after grouping of data by month in which I can expand or shrink a group of data (just like group/ungroup in excel). I do now want to use pivot table or sub-total functions.
 
Upvote 0
You asked for a macro.
Try this one. No extra columns needed.
Code:
Sub months()

Dim c As Range, a as Range

Set c = Range("A2")
Set a = c.Offset(, 2)

Do
If Evaluate("=month(" & c.Address & ")") <> Evaluate("=month(" & c(2).Address & ")") Then
    c(2).Resize(2, 3).Insert
    c(2).Offset(, 2) = "=sum(" & Range(a, c.Offset(, 2)).Address & ")"
    c(2).Offset(, 2).Font.Bold = 1
    Set c = c.End(4)
    Set a = c.Offset(, 2)
Else
    Set c = c(2)
End If
Loop Until c.End(4).Row = Rows.Count

c(2).Offset(, 2) = "=sum(" & Range(a, c.Offset(, 2)).Address & ")"
c(2).Offset(, 2).Font.Bold = 1

End Sub

Thanks Mirabeau for your quick reply.

I am after grouping of data by month in which I can expand or shrink a group of data (just like group/ungroup in excel). I do now want to use pivot table or sub-total functions. I have large volume of the data for which I want to use macro to create groups.
 
Upvote 0
I am after grouping of data by month in which I can expand or shrink a group of data (just like group/ungroup in excel).
Currently your data has no breaks between rows.
As a result, if you group your January dates, February dates and March dates of that sample data as it stands, you will end up with a single Group, not 3 groups.

To create separate groups you would need to add breaks in the data when the month changes.

You also want to sum the Amount column for each month. That sum could occupy a 'break' row between each month.

Subtotal automatically ...

- adds a new row between each month
- inserts the formula to sum the amount column
- provides outline symbols for each month which you can expand or shrink as normal groups

... which sounds like what you are after, so I'm not sure why you are so against using it?

Give this macro a try
Code:
Sub NPATEL()
  Application.ScreenUpdating = False
  Columns("A").Insert
  With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Formula = "=TEXT(B1,""mmm-yy"")"
    .Resize(, 4).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
      Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  End With
  Columns("B").SpecialCells(xlBlanks).Delete shift:=xlToLeft
  Columns("A").SpecialCells(xlConstants).Insert shift:=xlToRight
  Columns("A").Delete
  Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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