Trying to aggregate data by different date across worksheets
Closed
Exuma00
venkat1926
- Posts
- 1
- Registration date
- Thursday January 9, 2014
- Status
- Member
- Last seen
- January 9, 2014
venkat1926
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Related:
- Aggregate data in excel
- How to auto update data in excel from another worksheet ✓ - Forum - Excel
- How to match data in excel from 2 worksheets - Guide
- Copy data from one excel sheet to another: automatically - Guide
- How to auto populate data in excel from another worksheet - Guide
- Autopopulating multiple sheets from one main sheet ✓ - Forum - Excel
1 reply
venkat1926
Jan 10, 2014 at 03:31 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 10, 2014 at 03:31 AM
combine all the data from three sheets in one column in one sheet.
(columns A AND b)
in G1 type 1/1/14
'using "fill" function
fill the cells in column G with monthly incremental
how to do it
select G1 to G50
excel 2007
home ribbon-"fill" under editing (last)-choose "series"-
choose date on left and month on right and click ok
now format column G
custom----------mmm-yy
you see column G in the file(uploaded to speedyshare.com)
now run the macro in the module repeated here
download the file from
http://speedy.sh/TWsfR/exuma.xlsm
see column H
FEEDBACK PLEASE
(columns A AND b)
in G1 type 1/1/14
'using "fill" function
fill the cells in column G with monthly incremental
how to do it
select G1 to G50
excel 2007
home ribbon-"fill" under editing (last)-choose "series"-
choose date on left and month on right and click ok
now format column G
custom----------mmm-yy
you see column G in the file(uploaded to speedyshare.com)
now run the macro in the module repeated here
Sub test()
Dim rdate As Range, amt As Range, rmonth As Range, cmonth As Range, cdte As Range
Dim cfind As Range, mnth As Integer
Dim x As Double
Application.ScreenUpdating = False
Range("H1").EntireColumn.Delete
Set rdate = Range(Range("a1"), Range("a1").End(xlDown))
Set amt = rdate.Offset(0, 1)
Set rmonth = Range(Range("G1"), Range("G1").End(xlDown))
For Each cmonth In rmonth
x = 0
For Each cdte In rdate
If Month(cdte) = Month(cmonth) And Year(cdte) = Year(cmonth) Then
x = x + cdte.Offset(0, 1)
End If
Next cdte
cmonth.Offset(0, 1) = x
Next cmonth
Application.ScreenUpdating = True
End Sub
download the file from
http://speedy.sh/TWsfR/exuma.xlsm
see column H
FEEDBACK PLEASE