Excel gurus? Merging monthly and quarterly data - Miata Turbo Forum - Boost cars, acquire cats.

Welcome to Miataturbo.net   Members
 


Insert BS here A place to discuss anything you want

Reply
 
 
 
LinkBack Thread Tools Search this Thread
Old 09-30-2011, 03:14 PM   #1
Elite Member
Thread Starter
iTrader: (2)
 
Join Date: Sep 2008
Location: Central Florida
Posts: 3,284
Total Cats: 178
Question 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.
Attached Thumbnails
Excel gurus? Merging monthly and quarterly data-excel-merge-question.jpg  

Last edited by Scrappy Jack; 09-30-2011 at 03:27 PM. Reason: Clarified wording and attachment
Scrappy Jack is offline   Reply With Quote
Old 09-30-2011, 04:29 PM   #2
Elite Member
iTrader: (10)
 
Join Date: Oct 2008
Location: Seattle, Wa
Posts: 1,780
Total Cats: 30
Default

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?:
Jeff_Ciesielski is offline   Reply With Quote
Old 10-01-2011, 10:47 AM   #3
Elite Member
Thread Starter
iTrader: (2)
 
Join Date: Sep 2008
Location: Central Florida
Posts: 3,284
Total Cats: 178
Default

Quote:
Originally Posted by Jeff_Ciesielski View Post
Does this show what you're looking for?
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.
Attached Thumbnails
Excel gurus? Merging monthly and quarterly data-excel-output.jpg  
Scrappy Jack is offline   Reply With Quote
Old 10-01-2011, 12:23 PM   #4
Boost Pope
iTrader: (8)
 
Joe Perez's Avatar
 
Join Date: Sep 2005
Location: Chicago (Over two miles from Wrigley Field. Fuck the Cubs. Fuck them in their smarmy goat-hole.)
Posts: 26,315
Total Cats: 1,913
Default

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.
Attached Files
File Type: xls Book2.xls (14.5 KB, 40 views)

Last edited by Joe Perez; 10-01-2011 at 08:06 PM. Reason: clarification
Joe Perez is offline   Reply With Quote
Old 10-01-2011, 03:06 PM   #5
Crumple Zone Tester
iTrader: (7)
 
mgeoffriau's Avatar
 
Join Date: Jul 2009
Location: Jackson, MS
Posts: 7,656
Total Cats: 447
Default

You really kick back on Saturdays, huh Joe?
mgeoffriau is offline   Reply With Quote
Old 10-01-2011, 04:01 PM   #6
Boost Pope
iTrader: (8)
 
Joe Perez's Avatar
 
Join Date: Sep 2005
Location: Chicago (Over two miles from Wrigley Field. Fuck the Cubs. Fuck them in their smarmy goat-hole.)
Posts: 26,315
Total Cats: 1,913
Default

Quote:
Originally Posted by mgeoffriau View Post
You really kick back on Saturdays, huh Joe?
Hells yeah! We all makin' spreadsheets 'n numbaz up here in dis bitch!
Joe Perez is offline   Reply With Quote
Old 10-01-2011, 07:53 PM   #7
Elite Member
Thread Starter
iTrader: (2)
 
Join Date: Sep 2008
Location: Central Florida
Posts: 3,284
Total Cats: 178
Default

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.
Scrappy Jack is offline   Reply With Quote
Old 10-02-2011, 12:51 AM   #8
Boost Pope
iTrader: (8)
 
Joe Perez's Avatar
 
Join Date: Sep 2005
Location: Chicago (Over two miles from Wrigley Field. Fuck the Cubs. Fuck them in their smarmy goat-hole.)
Posts: 26,315
Total Cats: 1,913
Default

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.
Joe Perez is offline   Reply With Quote
Old 10-02-2011, 05:45 AM   #9
Elite Member
iTrader: (10)
 
Join Date: Oct 2008
Location: Seattle, Wa
Posts: 1,780
Total Cats: 30
Default

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
Jeff_Ciesielski is offline   Reply With Quote
Old 10-03-2011, 03:41 PM   #10
Elite Member
Thread Starter
iTrader: (2)
 
Join Date: Sep 2008
Location: Central Florida
Posts: 3,284
Total Cats: 178
Default

Thanks, guys! I cant' view or access dropbox.com from the office, but I got Joe's procedure to work.
Scrappy Jack is offline   Reply With Quote
 
 
Reply

Related Topics
Thread Thread Starter Forum Replies Last Post
Project Gemini - Turbo Civic on the Cheap Full_Tilt_Boogie Build Threads 57 07-19-2017 05:11 PM
1994 Spec Miata Race Car SM/SM2/SSM For Sale Quinn Cars for sale/trade 6 10-23-2016 08:58 AM
Momo Supercup seat jpreston Miata parts for sale/trade 2 05-17-2016 01:51 AM
3rd Time's a Charm...hopefully. zephyrusaurai Meet and Greet 2 09-28-2015 11:59 PM
WTB HPDE miata - Texas Voltwings Cars for sale/trade 0 09-27-2015 07:40 PM


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT -4. The time now is 03:58 AM.