Excel gurus? Merging monthly and quarterly data
09-30-2011, 02:14 PM
#1
Elite Member
Thread Starter
iTrader: (2)
Join Date: Sep 2008
Location: Central Florida
Posts: 2,799
Total Cats: 179
Excel gurus? Merging monthly and quarterly data
I know there are a bunch of 
on this site, so I thought I would toss this out there. I am trying to use Excel to run some calculations on two data sets. The problem is that one is monthly and one is quarterly.
I was wondering if there was some relatively automated way to merge the two so that the monthly data showed up at the appropriate date row in the quarterly worksheet.
See attachment for example portion of data sets. The lower version is what I would like it to look like.
Please and thank you.
on this site, so I thought I would toss this out there. I am trying to use Excel to run some calculations on two data sets. The problem is that one is monthly and one is quarterly. I was wondering if there was some relatively automated way to merge the two so that the monthly data showed up at the appropriate date row in the quarterly worksheet.
See attachment for example portion of data sets. The lower version is what I would like it to look like.
Please and thank you.
Last edited by Scrappy Jack; 09-30-2011 at 02:27 PM. Reason: Clarified wording and attachment
Reply
0
0
09-30-2011, 03:29 PM
#2
Let me see if I understand:
You have 2 worksheets.
One has monthly data (9/1/11 | 9/5/11 | etc)
The other has quarterly data(2011-Q1 / 2011-Q2 / Etc)
You want to perform calculations based on the quarter using the monthly data?
Does this show what you're looking for?:
You have 2 worksheets.
One has monthly data (9/1/11 | 9/5/11 | etc)
The other has quarterly data(2011-Q1 / 2011-Q2 / Etc)
You want to perform calculations based on the quarter using the monthly data?
Does this show what you're looking for?:
Reply
0
0
10-01-2011, 09:47 AM
#3
Elite Member
Thread Starter
iTrader: (2)
Join Date: Sep 2008
Location: Central Florida
Posts: 2,799
Total Cats: 179
No. I have data from two different workbooks which come from two different sources. Set A has data compiled monthly. Set B has it compiled quarterly, listed every April, July, October, etc.
I want to pair up the data for April, July, October, etc from each workbook.
I am sure that is still a little unclear. Hopefully the attached image better clarifies what I am talking about.
I want to pair up the data for April, July, October, etc from each workbook.
I am sure that is still a little unclear. Hopefully the attached image better clarifies what I am talking about.
Reply
0
0
10-01-2011, 11:23 AM
#4
Boost Pope
iTrader: (8)
Join Date: Sep 2005
Location: Chicago. (The less-murder part.)
Posts: 33,026
Total Cats: 6,592
It's not a macro, but I think this will do what you are asking for.
I started with the last image you posted, which assumes that all of the original source data has been merged into a single spreadsheet, that the original data is in chronological order, and that the monthly data is all at the top of the sheet while the quarterly data is all at the bottom:
(The colors are just there so we can keep track of which data is which moving forward.)
Now, I added a column to the left, and just auto-filled numbers from 1 to n into it as an index. These will be used later, to keep track of which data is monthly and which is quarterly (the monthly data will always have a lower index value than the quarterly data):
Next, I highlighted all the data (including the index) and did a Sort. I specified to sort by column B first (the date) and then by column A (the index):
The result was what you see below, where the data is all merged together, but wherever you have data for both a month and a quarter, the month is always on top because of the index.
Now, I add some more columns on the right. These columns all have formulas in them, I made it so you can see the formula in cell D2:
The formula for D2 is =IF((B2=B3),B2,""), the formula for E2 is =IF((B2=B3),C2,"") and the formula for F2 is =IF((B2=B3),C3,""). I created these just for Row 2, and then dragged then down to auto-fill the other rows. You can see in the image above the result. Whenever you have two sequential rows that contain the same date value, cells D, E and F for the first of the two rows populate themselves with the date and the two data values. The way the formulas work, you don't get duplicates.
Finally, to make it pretty, I selected all of the cells in the range of D2 to F11, copied them and then did a "Paste Values Only" into the next set of columns over. What this does is to paste the values you see on the screen instead of the formulas behind them. (You'll have to set the cell format in the newly-created date row column to date, as Excel will initially paste the date values as raw numerical data).
Once that's done, I just highlighted cells H2 to J11 and did another sort on column H:
The spreadsheet is attached.
I started with the last image you posted, which assumes that all of the original source data has been merged into a single spreadsheet, that the original data is in chronological order, and that the monthly data is all at the top of the sheet while the quarterly data is all at the bottom:
(The colors are just there so we can keep track of which data is which moving forward.)
Now, I added a column to the left, and just auto-filled numbers from 1 to n into it as an index. These will be used later, to keep track of which data is monthly and which is quarterly (the monthly data will always have a lower index value than the quarterly data):
Next, I highlighted all the data (including the index) and did a Sort. I specified to sort by column B first (the date) and then by column A (the index):
The result was what you see below, where the data is all merged together, but wherever you have data for both a month and a quarter, the month is always on top because of the index.
Now, I add some more columns on the right. These columns all have formulas in them, I made it so you can see the formula in cell D2:
The formula for D2 is =IF((B2=B3),B2,""), the formula for E2 is =IF((B2=B3),C2,"") and the formula for F2 is =IF((B2=B3),C3,""). I created these just for Row 2, and then dragged then down to auto-fill the other rows. You can see in the image above the result. Whenever you have two sequential rows that contain the same date value, cells D, E and F for the first of the two rows populate themselves with the date and the two data values. The way the formulas work, you don't get duplicates.
Finally, to make it pretty, I selected all of the cells in the range of D2 to F11, copied them and then did a "Paste Values Only" into the next set of columns over. What this does is to paste the values you see on the screen instead of the formulas behind them. (You'll have to set the cell format in the newly-created date row column to date, as Excel will initially paste the date values as raw numerical data).
Once that's done, I just highlighted cells H2 to J11 and did another sort on column H:
The spreadsheet is attached.
Last edited by Joe Perez; 10-01-2011 at 07:06 PM. Reason: clarification
Reply
0
0
10-01-2011, 06:53 PM
#7
Elite Member
Thread Starter
iTrader: (2)
Join Date: Sep 2008
Location: Central Florida
Posts: 2,799
Total Cats: 179
Damn, Joe. If you are ever in your old Florida stomping grounds, this is official notice of an IOU for a sitting of beer. 
Not just for the potential fix, but the level of detail in the explanation.
I left the spreadsheets I was working with at the office, but I will either access remotely tomorrow or double check on Monday. This looks like it could work.
Not just for the potential fix, but the level of detail in the explanation. I left the spreadsheets I was working with at the office, but I will either access remotely tomorrow or double check on Monday. This looks like it could work.
Reply
0
0
10-01-2011, 11:51 PM
#8
Boost Pope
iTrader: (8)
Join Date: Sep 2005
Location: Chicago. (The less-murder part.)
Posts: 33,026
Total Cats: 6,592
The number of things which Excel can do in a single-click is impressive, but it pales in comparison to the things which can be achieved by stringing together a few of the more basic functions in an interesting way.
Reply
0
0
10-02-2011, 04:45 AM
#9
I think I see what you're going for now. Allow me to propose an alternative solution:
This is the final output:
Now for how I accomplished it:
Much like Joe, I'm going to assume this data is on the same worksheet, if it isn't that's fine, but for this example it makes things a bit more clear (I'll attach an example workbook of everything on 3 different sheets).
The formula in column C3 is simply '=A17'(the beginning of your quarterly dates). I then copied and pasted that into c4, c5, etc. This gives you a list of dates to be matched up with ('index dates')
The formula in D3 is: 'sumif(a3:a14,C1,B3:B14)'
What this does is add the total value(B3:B14) of every row in the monthly data(a3:a14) where the monthly date matches your 'index date'(c1) in that row (quarterly date)
The formula in E3 is:'sumif(a17:a19,C1,B17:B19)'
It does the same thing as the formula above, but with the quarterly data.
Here is an example spreadsheet. If it doesn't open up, let me know (i created this on google docs since I don't have office install on this machine yet :|)
http://dl.dropbox.com/u/18380771/example.xls
This is the final output:
Now for how I accomplished it:
Much like Joe, I'm going to assume this data is on the same worksheet, if it isn't that's fine, but for this example it makes things a bit more clear (I'll attach an example workbook of everything on 3 different sheets).
The formula in column C3 is simply '=A17'(the beginning of your quarterly dates). I then copied and pasted that into c4, c5, etc. This gives you a list of dates to be matched up with ('index dates')
The formula in D3 is: 'sumif(a3:a14,C1,B3:B14)'
What this does is add the total value(B3:B14) of every row in the monthly data(a3:a14) where the monthly date matches your 'index date'(c1) in that row (quarterly date)
The formula in E3 is:'sumif(a17:a19,C1,B17:B19)'
It does the same thing as the formula above, but with the quarterly data.
Here is an example spreadsheet. If it doesn't open up, let me know (i created this on google docs since I don't have office install on this machine yet :|)
http://dl.dropbox.com/u/18380771/example.xls
Reply
0
0
Thread
Thread Starter
Forum
Replies
Last Post
