• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Redistribute time based data across more granular periods

S

sophie giesen

New Member
Hi

I have been given some data to work on a problem but it needs manipulating before I can start to work on it. Specifically, I have daily level data for approx 2 years which I need to split down to 15 min level data. I have a template for each interval for Monday-Friday (5 interval templates) but I haven't successfully been able to create a formula or macro to take the daily result and split it out based on the day of week in a single file. I have attached a sample file with tabs showing the daily results format and the templates, as well as an example of what I need the data to look like at the end of the process. Any help would be super appreciated!!! going a bit crazy on this one o_O

Thanks heaps!!!:)
 

Attachments

Hi Sophie ,

I think those trying to solve this may also go crazy.

The five templates all have percentages which add up to 100 % ; the values in the tab Ideal output example do not match date-wise with the values in the Daily results tab ; do you want that for every date mentioned in the Daily results tab , the value mentioned against the date should be split up according to the percentages given in the Templates tab ?

The tab Ideal output example has 45 values for each date , whereas the templates have 44.

The 45 values are between 7:45 and 18:45 ; is the split up to be for intervals between these 2 times ?

Narayan
 
Oh no!! So sorry my mistake! The timesteps should only be from 08:00-18:45, apologies. Yes each day's template should add up to 100% - for each daily result that is 100% of the redistribution proportionally. Does that make more sense? I have uploaded file again removing the incorrect timesteps - thanks for looking at this :)
 

Attachments

Ok, I think I worked out the redistribute part way though it's a bit clunky. I transposed the dates across the screen with the total daily result and then used =H$3*(VLOOKUP($A12,Templates!$A$2:$F$45,(MATCH(H$1,Templates!$A$1:$F$1,0)),FALSE)) as the formula to redistribute. Now though I need to get my data to be in a single column...is there an easy way to do this? I have updated my file with "working sheet" tab. Thanks so much again :)
 

Attachments

Hi Sophie ,

First , your data is voluminous , and I doubt that it will work over the 30000 rows that will be required for all the dates in the Daily results tab.

However , I have introduced the formulae in a few cells ; see if the results are OK.

I have removed your tab from the uploaded file because together the load is excessive.

Narayan
 

Attachments

Hi Narayan - this is awesome thanks!!!!! I have split into 3 files so the files are smaller and more manageable - totally amazing thanks so much!! :):):):):):)
 
You must log in or register to reply here.
Back
Top