Previous Topic Next Topic

Trying to aggregate data by different date across worksheets

Closed
Exuma00 Posts 1 Registration date Thursday 9 January 2014 Status Member Last seen 9 January 2014 - 9 Jan 2014 à 08:43
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 10 Jan 2014 à 03:31
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 response

Réponse 1 / 1
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
10 Jan 2014 à 03:31
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

Similar discussions

Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
HOW TO CALCULATE AGGREGATE OF STUDENT'S MARKS
peterappiahkubi -
Wofanat -

2 responses
How to extract data from one excel file to another
zamorin -
zamorin -

2 responses
Hosted app data will not clear
Woody_3336 -
woody_3336 -

7 responses