Previous Topic Next Topic

Trying to aggregate data by different date across worksheets

[Closed]
Report
Posts
1
Registration date
Thursday January 9, 2014
Status
Member
Last seen
January 9, 2014
 -
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
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

1 reply

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