Dear all,

OK, this one has me stumped. I have ten years of data. Each year is a separate tab in Excel. The first column in each tab is a combined date + time at hourly intervals (e.g. “5/1/15 03:00”, “5/1/15 04:00”, etc.) that runs for most of the year, but the tabs don’t necessarily begin or end on the same date/time, and some dates/times have been removed (such that every 24 rows is not necessarily a single full day, and it's different for every year). The second column is simply a value of interest.

What I’d like to do is create a matrix that has date/time (but not year) in the first column, with each subsequent column representing a data value for each year during that time. So, for example, on January 1 at midnight, and I’d like to know for each year (a) if there is a data value, and (b) what the data value is. Ideally, if there is no data value for that time during a particular year, the cell should remain empty.

Doing this manually with C&P would take too long because there are multiple gaps in the dataset, and they occur at irregular times for irregular lengths of time, so I need a way for Excel to pull data from each tab only when the date/time values match. I’m not sure if there is a formula for this or if it would require coding or if it is even possible to do in Excel. Any ideas?

Thanks,
R