Timeseries column cross-reference and matching?

aykadajiba

New Member
Joined
Jul 17, 2013
Messages
1
Hello all, I’m a new noob to the forum, would appreciate if anybody could provide some help.
I have 5 sets of columns, each includes a Time column and a Price column. However, the 5 individual Time columns of the 5 sets don’t match, i.e, the dates are not continuous (there are different time breaks in each set) and they begin from different starting dates.
I want to do two things, the second being optional (as I believe excel is incapable of delivering)
(1) Insert a new column set with common dates (the common dates/entries from Time1 to Time5) on the left and prices 1-5 on the right. E.g. (note the dates are NOT continuous)
Common Date
Price 1
Price 2
Price 3
Price 4
Price 5
20010304
938
33
988
5532
5732
20010308
25
23325
2358
664
8798
20010309
677
3
234
32423
234
20010311
9835
32434
3529
352
392
20010314
498
4534
43398
945
495483

<tbody>
</tbody>

(2) This is very different from (1); make the dates column continuous. That is, include the non-common dates as well by displaying the blank/breaking dates. E.g. (note the dates are continuous, no breaks, not even weekends)
Date
Price 1
Price 2
Price 3
Price 4
Price 5
20010304





20010305





20010306
677
3



20010307





20010308


234


20010309

9845


234
20010310



32423

20010311
43435




20010312

234


324
20010313





20010314
898





<tbody>
</tbody>

FYI, I’ve tried IF(COUNTIF()) commands, they simply sort out common entries within the same row. I messed around VLOOKUP and was too stupid to figure things out, the same goes for INDEX/MATCH. Perhaps the solutions lie within PivotTable or VBA, of which I have zero knowledge.
I’m using Excel2013, I guess I can't attach the file-of-concern here.... Thanks!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,118,662
Messages
5,573,478
Members
412,532
Latest member
crazzyapple
Top