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)
[TABLE="width: 416"]
<tbody>[TR]
[TD]Common Date
[/TD]
[TD]Price 1
[/TD]
[TD]Price 2
[/TD]
[TD]Price 3
[/TD]
[TD]Price 4
[/TD]
[TD]Price 5
[/TD]
[/TR]
[TR]
[TD]20010304
[/TD]
[TD]938
[/TD]
[TD]33
[/TD]
[TD]988
[/TD]
[TD]5532
[/TD]
[TD]5732
[/TD]
[/TR]
[TR]
[TD]20010308
[/TD]
[TD]25
[/TD]
[TD]23325
[/TD]
[TD]2358
[/TD]
[TD]664
[/TD]
[TD]8798
[/TD]
[/TR]
[TR]
[TD]20010309
[/TD]
[TD]677
[/TD]
[TD]3
[/TD]
[TD]234
[/TD]
[TD]32423
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]20010311
[/TD]
[TD]9835
[/TD]
[TD]32434
[/TD]
[TD]3529
[/TD]
[TD]352
[/TD]
[TD]392
[/TD]
[/TR]
[TR]
[TD]20010314
[/TD]
[TD]498
[/TD]
[TD]4534
[/TD]
[TD]43398
[/TD]
[TD]945
[/TD]
[TD]495483
[/TD]
[/TR]
</tbody>[/TABLE]
(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)
[TABLE="width: 416"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Price 1
[/TD]
[TD]Price 2
[/TD]
[TD]Price 3
[/TD]
[TD]Price 4
[/TD]
[TD]Price 5
[/TD]
[/TR]
[TR]
[TD]20010304
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010305
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010306
[/TD]
[TD]677
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010307
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010308
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010309
[/TD]
[TD]
[/TD]
[TD]9845
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]20010310
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]32423
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010311
[/TD]
[TD]43435
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010312
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]324
[/TD]
[/TR]
[TR]
[TD]20010313
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010314
[/TD]
[TD]898
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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!
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)
[TABLE="width: 416"]
<tbody>[TR]
[TD]Common Date
[/TD]
[TD]Price 1
[/TD]
[TD]Price 2
[/TD]
[TD]Price 3
[/TD]
[TD]Price 4
[/TD]
[TD]Price 5
[/TD]
[/TR]
[TR]
[TD]20010304
[/TD]
[TD]938
[/TD]
[TD]33
[/TD]
[TD]988
[/TD]
[TD]5532
[/TD]
[TD]5732
[/TD]
[/TR]
[TR]
[TD]20010308
[/TD]
[TD]25
[/TD]
[TD]23325
[/TD]
[TD]2358
[/TD]
[TD]664
[/TD]
[TD]8798
[/TD]
[/TR]
[TR]
[TD]20010309
[/TD]
[TD]677
[/TD]
[TD]3
[/TD]
[TD]234
[/TD]
[TD]32423
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]20010311
[/TD]
[TD]9835
[/TD]
[TD]32434
[/TD]
[TD]3529
[/TD]
[TD]352
[/TD]
[TD]392
[/TD]
[/TR]
[TR]
[TD]20010314
[/TD]
[TD]498
[/TD]
[TD]4534
[/TD]
[TD]43398
[/TD]
[TD]945
[/TD]
[TD]495483
[/TD]
[/TR]
</tbody>[/TABLE]
(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)
[TABLE="width: 416"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Price 1
[/TD]
[TD]Price 2
[/TD]
[TD]Price 3
[/TD]
[TD]Price 4
[/TD]
[TD]Price 5
[/TD]
[/TR]
[TR]
[TD]20010304
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010305
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010306
[/TD]
[TD]677
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010307
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010308
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010309
[/TD]
[TD]
[/TD]
[TD]9845
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]20010310
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]32423
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010311
[/TD]
[TD]43435
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010312
[/TD]
[TD]
[/TD]
[TD]234
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]324
[/TD]
[/TR]
[TR]
[TD]20010313
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20010314
[/TD]
[TD]898
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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!