Trying to aggregate data by different date across worksheets
Closed
Exuma00
Posts
1
Registration date
Thursday January 9, 2014
Status
Member
Last seen
January 9, 2014
-
Jan 9, 2014 at 08:43 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 10, 2014 at 03:31 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 10, 2014 at 03:31 AM
Related:
- Aggregate data in excel
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Tmobile data check - Guide
- Dd-mm-yyyy format in excel - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 10, 2014 at 03:31 AM
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
