Nah… Excel doesn’t have a “hot date” function. I feel a little silly asking this, but I often run across this problem and am sure that I am not solving it very efficiently. I’m wondering if any of you have easier ways of handling it. I have time series returns for two securities, and I want to compute the daily correlation between them. X_US is US based and has one sequence of dates. X_EU is european based and has another. Current format is: ColumnA=Date; ColumnB=X_US; ColumnC=Date; ColumnD=X_EU. Problem is, the trading days don’t line up, so by the end of the sequence, the same row has different dates for X_US and X_EU. Question: What’s the simplest way to get the sequence massaged so that you have ColumnA=Date; ColumnB=X_US; ColumnC=X_EU; such that X_US and X_EU all apply to the date in ColumnA. Answers can have dates with missing data, or they can be all dates that have two valid data points.
I ran into the same issue when taking data from yahoo finance and another source for different indices. Interested in the solution.
I would make a new sheet (sheet2) that has the three columns you want and put every date in the date range in ColumnA. Then us the following formula in ColumnB: =if(iserror(vlookup(a2,sheet1!$a$1:$b$1000,2,false))=TRUE,"",vlookup(a2,sheet1!$a$1:$b$1000,2,false)) in columnC: =if(iserror(vlookup(a2,sheet1!$c$1:$d$1000,2,false))=TRUE,"",vlookup(a2,sheet1!$c$1:$d$1000,2,false)) also probably not the most efficient way so I’m curious to see what others think.
^ that’s the way i’d do it.
What I usually do is line up every date in the [first,last] range in a column and then place the two columns of dates additional columns next to them. (Hmm… there was some sorting I did to get the dates to line up right again… heck, I forget) Then I’ll place a “1” in a column every row that has no blanks and “0” in other columns. I make a second copy of that column and line up a 1/0 column next to each of the original date sequences (you may need to do a sort to remove all the empty dates again). Then I do a sort and the things line up. But every time I do it, I have to reinvent the wheel, and it’s clunky. And since I don’t do it all that often I find myself thinking “how did I do that again?” Looking for better ways.
I have the exact same problem. Extremely annoying and time consuming. Hopefully someone has a solution.
make the formulas in the new sheet: in columnB =if(iserror(vlookup(a2,sheet1!$a:$b,2,false))=TRUE,"",vlookup(a2,sheet1!$a:$b,2,false)) in columnC: =if(iserror(vlookup(a2,sheet1!$c:$d,2,false))=TRUE,"",vlookup(a2,sheet1!$c:$d,2,false)) then all you have to do is update the dates on the new sheet and put the new data in the same columns as before and it will update.
Yeah, VLOOKUP does the trick nicer than my method… Thanks. I’m constantly overlooking VLOOKUP. Gotta stop that.
totally agree with Vlookup, one of my most used functions 
Thanks guys, VLOOKUP is my new tool… I used KrukVT’s formulation almost verbatim and it worked great.
I use vlookup more than any other function I think. I also use INDEX and MATCH together to look up by row and column a lot. Edit: Here is a link that explains how to do this. http://www.wall street prep.com/blog/?blog_id=28 remove the spaces