Previous Topic Next Topic

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 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 10, 2014 at 03:31 AM
Hi,
I am attempting to consolidate data across different worksheets in a workbook, that having different dates.
For example, worksheet "Cert1" has the following dates/data points

7-Mar-14 44.07
7-Sep-14 44.80
7-Mar-15 44.07
7-Sep-15 44.80
7-Mar-16 44.32
7-Sep-16 44.80
7-Mar-17 44.07
7-Sep-17 44.80

Worksheet "Cert2" has the following dates/data points:

26-Apr-14 185.43
26-Oct-14 186.45
26-Apr-15 185.43
26-Oct-15 186.45
26-Apr-16 186.45
26-Oct-16 186.45
26-Apr-17 185.43
26-Oct-17 186.45

Next worksheet "Cert3" has the following dates/data points:

12-Jun- 14 66.22
12-Dec-14 66.59
12-Jun-15 66.22
12-Dec-15 66.59
12-Jun-16 66.59
12-Dec-16 66.59
12-Jun-17 66.22
12-Dec-17 66.59

I would like to add on a consolidated monthly table, so that I know all the data for each month (ignoring the day) of each year covered by the data.

The table should look like this:
Jan-14 0
Feb -14 0

etc. There will be ocassions where there are data ppoints for the same month across the worksheets.

Thanks!
Mar-14 44.07
Related:

1 reply

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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 


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
0