Please help me! (Adjusting time-series)

[Roland]

Hi!

I have a big problem in Excel (version 2003). I wonder if you could help me.
I have 2 very long time-series in coloumns B and D, and the dates of these
daily datas in coloumns A and C. They start at the same day, but the problem
is, that while the first time-series has a value for every day, the second
one skips weekends and holidays, so by the end there is a really wide time
gap between the dates in a single row. However I must do statistical
comparison for datas of the same day, so I'd like to adjust the second series
to the first one. The missing days of the second series would take the values
of the last real data. Doing this manually would take many hours, so I'm
desperate. I really don't know if there is a solution, but I would really
appreciate any help! Please helo me!

Thanks in advance!
Roland

 

[MartinW]

Hi Roland,

Assuming your data starts at row 2 and your column A dates finish in A100
put this in E2 and drag down, ( change the 100 to whatever is your last
cell)
=IF(ISERROR(VLOOKUP(A2,$C$1:$D$100,2,FALSE)),"",VLOOKUP(A2,$C$1:$D$100,2,FALSE))

HTH
Martin

 

[Roland]

Hi MartinW,

Thank you so much, you are great!!!

Bye,
Roland

 

[MartinW]

Glad it worked Roland,

Just for correctness the $C$1 references should have been $C$2
although that won't affect the funcionality in this case.

Regards
Martin

 

[Roland]

Thank you for your comment MartinW, I've realized that minor mistake. And I
also realized, that if I write "E1" in the middle instead of "", I can get
the Excel to put Fridays' data in the place of Saturdays' and Sundays', as I
wanted originally. (As there is no data on weekends, it would be blank, which
is not good for me).

Thanks again, you made my day happier!
Have a nice day too!

Roland