Rearranging an time series with missing dates

Hello,

I need some help to rearrange a time series wich has some missing dates.
The attached file has a exemple of my problem; it's better to see it to understand.

The original information ("date" and "temperature") has some missing days.
So, I need to make two corrected columns ("result") that consider these null values​​, based on the "reference date".

The idea of the algorithm seems to be easy (I think, but I don't know how to do it):

1. The VB macro should check if the date is equal to the reference date;
1.1 If equal, hence it takes this value;
1.2 If not equal, hence it have to do an iteration in which the cell column scroll down until it find the next cell with the same date value.

I hope that somebody understand my problem and help me to solve this.
My apologies about any English mistake.

Thank you very much!


date.xls

Dear Søren,

Thank you for the fast reply.
No value has to change on the column E. The E4 and E6 stay blank, no calculation is needed. It only has to maintain the correspondence with the original day.
The expected result is the columns D and E, just the way it is shown at this worksheet.
The columns D and E are the reordering/rearranging of the original values, but now considering the null days (e.g. D4, E4; D6, E6; D8:D9... they all stay with blank values) which are not shown on the original data.
So, the result columns are the original date + blank days.

Thank you!
Best greetings.

And to be clear with my problem...
The example I sent it's only one piece of 300 spreadsheets that have, each one, more than 50,000 lines with data.
That's why It would be necessary a VB script to facilitate the work.

WOOOW!!! This is amazing!
It worked like a magic! Perfect!!

Thank you so much, tigeravatar!!!

Originally Posted by tigeravatar

bafuncio,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

Sure I will!
That macro is the best, really!
Thank you!!

Originally Posted by Søren Larsen

Since you are using Excel 2010 you might as well use:

Formula:

Please Login or Register  to view this content.

But as you have 50,000 rows of data and 300 files, you'll be better of with the macro.