Fill Column Based on Actual Time

[haiders7]

Hello Haider,
Welcome to excelfox.

Your file link, https://drive.google.com/file/d/1drG...ew?usp=sharing , ........
does not work.

Thanks alot for your effort and apology that you were not able to download a file.
I have attached a zip file for your review.

Many Thanks
Haider

[DocAElstein]

Hello Haider,
For the next time, please try to give a much more reduced size data sample.- Usually coding written to work on a small set of data will work with little or no modification on a larger sample… But it is a lot easier to develop and explain a solution at a forum with a much smaller sample.
_.___
Anyway I have taken a look…
I don’t really understand what is going on: I have re read your post and looked at the file a few times. I have no idea about how these things are organised, or work, so it is all unclear to me. I expect I would need a much more detailed description of the thing to get properly up on speed. I am not totally clear how the logic which I see in your example data ties up with what you have described.

( Things like this, for example … MidBreak: Tells about the Mid Break in an Hour. For e.g MidBreak-1, MidBreak-2, MidBreak-3, etc. etc.
Break Start and Break End: These columns tells about the Break time start and Break Time End in a prticular time slot. … that says nothing to someone that does not know your project. Maybe it is not relevant to what you want. If that is the case, then by all means mention it, but say that it is not relevant to what you want. Otherwise it can confuse. Well me at least, - admittedly that does not take much to confuse me.. )

You say the Sheet1, (“New Time” column), is populated from Sheet2. I can possibly see how. I do see some highlighted values in Sheet2, ( BTW it would have been nice to tell me that, just to help me realise quicker). But it seems strange to me that your “New Time” is just your “AdStart” from Sheet2

For example, here are some lines from you file:
http://www.excelfox.com/forum/showth...ll=1#post11125
I will work on that simple logic, as seen in those screenshots, for now. I do not think I quite follow exactly your logic yet… _
_... But I will make a start, ….

---

My Basic strategy and coding description
Rem 1
'1b) I put your data into arrays, ( arraysd are just like fixed size worksheets which you don’t see. That just makes things work bit more quicker, as VBA is a bit slow with lots of data when it manipulates a worksheet. (VBA allows me to first capture a data range to an array , and then finally paste out in one go an array to a worksheet, which is very fast compared to continually manipulating cells in a worksheet ) )
'1b)(ii) I add an extra last dimension ( pseudo an extra “column”) to the Output array for worksheet, “Sheet1”, to allow for your wanted “New Time” column. ( Fortunately, VBA lets us add an extra last dimension ( pseudo an extra “column”) , so that is handy.. )
Rem 2
I make two extra simple arrays, one corresponding to each Worksheet. They are single “width” arrays, and their elements are the concatenation of Channel Name & Date & Time
(We need those to do the search to find the match from each row in Sheet 1 in the Sheet2)
However, this is the stumbling point for me : Either I have missed something, or you have missed something in your explanation. Or a combination of both. It escapes me where the logic is to match a row in Sheet2 with a row in sheet1 (I am stumbling to find the logic to match up these rows.... http://www.excelfox.com/forum/showth...ll=1#post11126 )
So for now I have just done an approximate match.**
Rem 3
What we are effectively doing here is taking each row in Sheet1 in turn, and then
‘3a) looking for the matched concatenation of „ Channel Name & Date & Time “ (for now I have just done an approximate match.** )
Once we have a match,
‘3b) We fill the last dimension (“column” , “New Time” ), in the output array with the “AdStart” at the matched “row” from the Sheet2 data.
Rem 4
We should have now the final results something like what we want in the output array, so we simply paste the entire array out.

---

I will take another look here tomorrow, to see if you have replied. But if you need more help, I think you will have to be a lot more clearer about what you want: Remember – you know your project, so your description will sound OK to you. If there is more to it then I have been able to see, then you will need to explain it very carefully.
You need to walk me through and explain exactly how you get the sample Output you gave on Sheet1, column D. I assume it comes from the Sheet2 data, but exactly how is not clear to me yet.….(I am stumbling to find the logic to match up these rows. : http://www.excelfox.com/forum/showth...ll=1#post11126

I may just have missed something obvious, it would not have been the first time, lol…. . If that is the case, then I leave it to you to set me straight….

Alan

---

Here is the provisional coding: http://www.excelfox.com/forum/showth...ll=1#post11127
I have not fully ‘commented and explained the coding yet, as I am expecting it is not yet what you finally want.


This is the start of the results it gives. I don’t think they are the results you want. I think I need more info from you in order to get the results that you do.
_____ Workbook: Data Sheet.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	Date	Channels	start time
2	11.15.2017	A NEWS	20:29:00	20:27:03
3	11.15.2017	A NEWS	20:59:00	20:58:42
4	11.15.2017	A NEWS	21:29:00	21:14:41
5	11.15.2017	A NEWS	21:59:00	21:58:48
6	11.15.2017	A NEWS	22:29:00	22:28:46
7	11.15.2017	A NEWS	22:59:00	22:58:48
8	11.15.2017	A NEWS	23:29:00	23:28:33
9	11.15.2017	A NEWS	23:58:00	23:57:57

Worksheet: OutputTest

[haiders7]

.....I have taken a look…......

Hi

Thanks again for all your effort.

I explain you in more detail now that what actually Sheet1 and Sheet2 are.

Sheet1: It is the plan sheet where the spots are place on a particular channel on specific date and specific time. Specific Time (Start Time) is the automated time generated from some other source. The New Time is the time we actual want from the historic data which is placed in Sheet2.

Sheet2: It has the historic data of all spots that were aired on different channels. So whenever the new plan is going to happen we have to pick an actual Ad time of the Ad from the historic data. Lets assume we have the spots plan in Sample Sheet and then we have output mentioned in Sample Sheet. In the output you will see that all New Time comes from the Sheet2 Data. It could be done through Random matching or some other logic but point is here that we have to pick the actual time that is available in Sheet2 Data.

MidBreak Start Time and End Time columns are for your info. If you want to use those columns for your logic then you can also use.


I have attached the zip file (Data Sheet v2.zip) so you can see my sample sheet as well.

One more thing to add, if in any case there is no spot aired in Sheet2 on a specific channel, date and timeslot then New Time will be same as start time.
In Sample sheet, I planned 8 spots (maximum number of spots plan in an hour) which will help you in designing a generic logic.

Feel free to any in case of any ambiguity.

Thanks
Haider










Basically, the output I am expecting is