Consolidating Data Based on Date

[Acem13]

Hi everybody, I'm currently working on a research project that I desperately need help with. What I have is a list of companies, the date that they issued a dividend, and the amount of the dividend that they issued. I'm trying to consolidate the data down from being listed daily, to being listed quarterly. The problem though is that even though the dates are listed chronologically for each specific company, the dates and amounts are all random from company to company. There is 25 years of data for 720 companies, so manually sorting it would be too time consuming. So how do I get excel to consolidate down the data automatically based on a specific date range? I want it to take any dividends that were issued between Jan 1 and Mar 31 and put their total sum value into Quarter 1 for that specific year, for each company. I want quarter 2 to be defined as any dividends issued between Apr 1 and June 30, quarter 3 to be any dividends issued between July 1 and Sep 30, and quarter 4 to be defined as any dividends issued between Oct 1 and Dec 31. If there were no dividends issued in a given quarter, than I want excel to assign that quarter a value of 0. The daily dividend amounts for each company are shown on the dividend payout worksheet, and I'm trying to consolidate those values on the Quarterly Dividend worksheet. I was thinking maybe it could be done with a sumif function? Several people told me that it could be done with a macro, but I have absolutely no idea how to write macros. Any help would be very greatly appreciated!

[Pete_UK]

It's going to be a bit awkward as you have data on the first sheet occupying two rows per company and your data on the second sheet is just on a single row. It will be easier if you insert a blank row between each company on your second sheet, and then the formulae can be copied to each alternate row - afterwards, those blank rows can be deleted again (or hidden).

Another awkward thing is that you have your years in a merged cell covering 4 columns, but as you need to specify the dates for each quarter this means that you basically need 4 formulae which can then be copied as a block. I've put these formulae in the cells stated below:

C4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,10,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,12,31))
D4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,7,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,10,31))
E4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,4,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,6,30))
F4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,1,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,3,31))

Hopefully you can spot the date ranges in the formulae. Then this block of 4 cells can be copied and pasted into cells G4, K4, O4, S4 etc across that row (I've only done it up to S4 - it's YOUR research project, after all !! <bg>).

I've then inserted a new row 5, and copied the formulae from row 4 directly into row 6. You will need to continue to do this, both across the columns and then into alternate rows (with blank rows inserted) for all of your companies. This assumes that your companies on the second sheet are in exactly the same order as those on the first sheet.

I'm not sure why you had save the example file as a macro-enabled workbook (.xlsm) when there wasn't a macro in there, so I've made the attachment an .xlsx file.

Hope this helps.

Pete

[FDibbins]

Trying to figure something out for you. The merged 2 rows on the 1st sheet, combined with only 1 row per item on the 2nd sheet are complicating things

[Acem13]

It's going to be a bit awkward as you have data on the first sheet occupying two rows per company and your data on the second sheet is just on a single row. It will be easier if you insert a blank row between each company on your second sheet, and then the formulae can be copied to each alternate row - afterwards, those blank rows can be deleted again (or hidden).

Another awkward thing is that you have your years in a merged cell covering 4 columns, but as you need to specify the dates for each quarter this means that you basically need 4 formulae which can then be copied as a block. I've put these formulae in the cells stated below:

C4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,10,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,12,31))
D4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,7,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,10,31))
E4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,4,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,6,30))
F4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,1,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,3,31))

Hopefully you can spot the date ranges in the formulae. Then this block of 4 cells can be copied and pasted into cells G4, K4, O4, S4 etc across that row (I've only done it up to S4 - it's YOUR research project, after all !! <bg>).

I've then inserted a new row 5, and copied the formulae from row 4 directly into row 6. You will need to continue to do this, both across the columns and then into alternate rows (with blank rows inserted) for all of your companies. This assumes that your companies on the second sheet are in exactly the same order as those on the first sheet.

I'm not sure why you had save the example file as a macro-enabled workbook (.xlsm) when there wasn't a macro in there, so I've made the attachment an .xlsx file.

Hope this helps.

Pete

Thank you so much!!! I need to get all of this data consolidated before I can even begin the process of analyzing it which is going to take a while. I followed your advice and ran it through a couple of companies and its working perfectly! Very much appreciated!!

[Acem13]

One last question, how do I mark this thread as solved?

[royUK]

Please don't quote whole posts unless necessary.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Pete_UK]

I've just spotted that the date range for the second formula is slightly out - the range covers 1st July to 31st October, but should only go up to 30th September. So, you need to change the formula in D4 to this:

D4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,7,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,9,30))

If you have already copied those formulae into place, then you can make the changes to all of them in one operation using Find & Replace. Highlight all the cells with the formula in, do CTRL-H to bring up the dialogue box, then:

Find what : ,10,31))
Replace with: ,9,30))
Click Replace All

Hope this helps.

Pete