Fill Column Based on Actual Time

Dear All

Need an urgent help to get the below scenario sorted.

I have attached 2 sets of Data.

In Sheet2, there is a TV Ad data aired on different channels. Explanation of each column below

Channel: Name of the Channel
Date: Current Date
AD Start: TV AD start Time
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.

In Sheet1, I need an output on New Time Column. This New Time column will populate from Sheet 2 through its Channel Name, Date and Time Slot. New Time column output would be any random time on a specific Channel, Date and Time Slot. I have mentioned few example in the below link in excel.

https://drive.google.com/file/d/1drG...ew?usp=sharing


Thanks,

Haider

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.eileenslounge.com/viewtopic.php?p=311844#p311844
https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe
https://www.eileenslounge.com/viewtopic.php?p=311826#p311826
https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783
https://www.eileenslounge.com/viewtopic.php?p=310916#p310916
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720
https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171
https://www.eileenslounge.com/viewtopic.php?p=310110#p310110
https://www.eileenslounge.com/viewtopic.php?p=310024#p310024
https://www.eileenslounge.com/viewtopic.php?p=309121#p309121
https://www.eileenslounge.com/viewtopic.php?p=309101#p309101
https://www.eileenslounge.com/viewtopic.php?p=308945#p308945
https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hello Haider,
Welcome to excelfox. :)

Your file link, https://drive.google.com/file/d/1drG...ew?usp=sharing , does not work for me. I tried on a couple of different computers and systems, but I just get this:
haiders7 File link not working.JPG : https://imgur.com/SlRCRyi
Attachment 2234


That does not surprise me , because you have actually posted this text: https://drive.google.com/file/d/1drG...ew?usp=sharing. That is not a typical full URL.
It looks to me as though you have pasted in a shortened view from as seen in a forum post, and have not checked to see that you have the correct full URL pasted

My guess is that, possibly, as you are requiring urgent help then you may have posted somewhere else, and in your haste you have just done a quick copy of another post you have somewhere??? . Or you edited but pasted in a copy of the original post…
In either case , the link is obviously not a valid one.

You will likely not get quick help here, because it is Easter Holiday, and not many people are here at the best of times. I will look in here again tomorrow. If you have corrected the link by then, and confirm that you still need help tomorrow, then I may have some time tomorrow to look at this

Please let us know if you find the solution elsewhere in the meantime so I don’t waste my time.

Thanks,
Alan

Quote:

---

Originally Posted by DocAElstein

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

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

Quote:

---

Originally Posted by DocAElstein

.....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

Hello Haider,
OK. I think I understand the logic now.
I think I needed this extra information. Either it was missing from, or I missed it / it was not clear enough , in your initial explanation.
I think I can do this for you.
But, If I use the current data file, then I will need a lot of time to check and debug and develop the code. As I mentioned already before… _

Quote:

---

Originally Posted by DocAElstein

.....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. ....

---

_……

So, If you want me to do this for you, then please supply me a reduced sample of data, so that I can develop the solution and include screenshots in the forum post. ( I could do this for you , but I do not have a lot of time. It is best that I spend the time doing what you cannot do).

Reduce the data in Sheet1 to about 3 sets of data, so approx (3x8) + Header = approx 25 lines
In Sheet2 include the data needed for sheet1, and possibly just a little bit more

You just need enough data to test out all scenarios. Choose your test data carefully so that all possible scenarios are included. Make the data realistic, but just include enough data to demonstrate all possible scenarios.

I have no more time today. I must go off line now until Wednesday. I will look here again on Wednesday.

If this is not quick enough for your urgency, and you find a solution elsewhere before Wednesday , then please let me know, so that I do not waste my time.

Thanks

Alan


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Quote:

---

Originally Posted by DocAElstein

Hello Haider,
OK. I think I understand the logic now.
I think I needed this extra information. Either it was missing from, or I missed it / it was not clear enough , in your initial explanation.
I think I can do this for you.
But, If I use the current data file, then I will need a lot of time to check and debug and develop the code. As I mentioned already before… _ _……

So, If you want me to do this for you, then please supply me a reduced sample of data, so that I can develop the solution and include screenshots in the forum post. ( I could do this for you , but I do not have a lot of time. It is best that I spend the time doing what you cannot do).

Reduce the data in Sheet1 to about 3 sets of data, so approx (3x8) + Header = approx 25 lines
In Sheet2 include the data needed for sheet1, and possibly just a little bit more

You just need enough data to test out all scenarios. Choose your test data carefully so that all possible scenarios are included. Make the data realistic, but just include enough data to demonstrate all possible scenarios.

I have no more time today. I must go off line now until Wednesday. I will look here again on Wednesday.

If this is not quick enough for your urgency, and you find a solution elsewhere before Wednesday , then please let me know, so that I do not waste my time.

Thanks

Alan

---

Hi Alan

I have attached all the scenarios along with the data in the attached file. I highlight the scenarios in different colors so you can understand and check it with Sheet2 as well. Last scenario of D news has no data therefore in that scenario start time will be the New time.

Feel free to ask in case of any ambiguity and yes I can wait for next couple of days.

Many Thanks
Haider

Quote:

---

Originally Posted by haiders7

..
I have attached all the scenarios along with the data in the attached file. I highlight the scenarios in different colors so you can understand and check it with Sheet2 as well. Last scenario of D news has no data therefore in that scenario start time will be the New time....

---

Thanks, Haiden.
At first glance, that seems to make things a lot clearer.
( It seems that the there are now not just groups of 8 in Sheet1 ? )
The logic needed, generally, is quite complicated, that is to say a few things need to be done to get things into the right grouping. But it does not look difficult.
It just needs a bit of careful thought. I think I can do this.

I will post back later in the week, when I should have a solution for you

Alan

Hi Haider
Here is a routine for you to try, Sub HaiderAdSlots_v3()
http://www.excelfox.com/forum/showth...ll=1#post11146

---

I will start here in this post now, with explaining how I see what you want. Remember I am starting from scratch with your project, so I may have some terminology not quite right… Hopefully I am repeating what your description, or similar. If I have anything wrong in this description below, then that will be reflected in the routine probably not quite doing what you want. So me doing this explanation of as I see it, will help us to fix what is wrong…

What is going on, my logic, Problem description..etc....
Groups of data
As far as I can see, the “Input” ( or “historical” ) data, “Sheet2” can be considered to be divided up into “hourly sections”, (possibly what you call a “time spot”? , I am not sure). Your sample Data with highlighted sections shows the groupings, ( here is a shortened version of Sheet2 from the version 3 file, “Data Sheet v3.xls”
http://www.excelfox.com/forum/showth...ll=1#post11140
http://www.excelfox.com/forum/showth...ll=1#post11141
http://www.excelfox.com/forum/showth...ll=1#post11142
http://www.excelfox.com/forum/showth...ll=1#post11145
http://www.excelfox.com/forum/showth...ll=1#post11144 )
( I am thinking additionally , that possibly different news channels may not be considered to belong to the same grouping.)
The end result of that little bit of thinking of mine, is that I will use a concatenation of the channel and hour as the “group identifier” , like “A NEWS” & “20”
On further thoughts, I will also add the date into the group identifier, so will have like
“A NEWS” & “15.11.2017” & “20”
This will be used to identify to which “group” the rows in Sheet1 ( http://www.excelfox.com/forum/showth...ll=1#post11143 ) , belong.
If I have understood correctly , then your sample data in your file version3 , sheet2 , has 6 groups, each one you have highlighted in a different color.
Groups grouped together.
I am going to make an assumption that the different groups are grouped together, as in the test data, version3. This simplifies the logic and coding. ( But it would not be difficult to allow for the input historical data being all mixed up – a bit more coding would be necessary, that’s all )
New Time in Sheet1, where does it come from..
In the output data , Sheet1 , of you sample data, one column, D , is blank. Filling that “New Time” column is the main thing that the coding should do. That is what it is all about, … as far as I can tell...
Once we determine to which group the row in Sheet1 belongs, the New Time in column G is to be based on a random selection from the previous , ( “historical data” ) for that particular group. Specifically we pick a time from the column C, Sheet2 , AdStart from that group. ( At first glance it appears to me that we have no duplicated AdStart times. – That might be obvious if you know the project, I guess… but for later refinement you might want to consider how the coding should react if duplicates did occur… even if theoretically it shouldn’t. ( If you are sure that such a scenario will never occur, then “Sod’s Law” tells us that it will actually occur at the most inconvenient time for you :)… )

I expect you probably don’t want to have any “New Time”s that are the same. But just for simplicity, I have not allowed for that just yet. So the current code could give duplicate start times. - I think the coding is quite a lot to take in at once, so I did not want to further complicate things yet.
If the current coding is OK, and you confirm that you do not want any duplicated “New Time”s , then it should be easy to modify the coding for that. I can do that for you if you can’t figure out how.

I think that covers the description of the problem for now.

---

Brief coding description.
Rem 1
This is the basic collecting of data. As I explained before, I like to use ( 2 dimensional ) arrays for this sort of coding. Just a personal preference. I work then internally on the data, and only interact a couple of time with the worksheet,
_ when I take in all data in one go into a main data array,
and then
_ finally paste out all the output in one go from an output array, the filing of which is the main thing the coding is doing.
( The output array in this case is almost full to start with: It is the initially Sheet1 data , only missing the last column, column G , New Time )
In this code section it is convenient to get the row size, Lr_ of both data worksheets. This step, along with the use of the variables , Lr_ , in the coding is basically what make the coding work generally for any size of data.

String Split to Array idea
Before I start explaining the next sections, I want to remind myself of a general way that I like to use to collect things into an list or array. As example, consider the last group, ( highlighted orange in your sheet2 historical data… I need to collect that column C AdStart time in order to get a random selection from it later.
I prefer to collect that in a single string , “ 22:01:33 22:02:01 22:02:16 ….etc “
My preference is because I there are a lot of simple nice VBA string functions to manipulate that, including a particularly useful one, the Split function, ( https://docs.microsoft.com/en-us/off...split-function ) , which allows me to easily convert that into a 1 dimensional array, pseudo like
VBASplit ( “22:01:33 22:02:01 22:02:16” ) = {“22:01:33”, “22:02:01”, “22:02:16”}
That further allows me to access easily individual parts of the array list through its number index, with pseudo like
array(0)= “22:01:33”
or
array (1)= “22:02:01”
or
array (2)= “22:02:16”

Rem 2 Building arrays to hold the concatenated identification, Id, string for each row of data in each worksheet.
Similar to my last routine. But new this time, I also make a third array, arrEnucsIds(), that just has one of each of the different concatenated Id stings, So this has the list of unique concatenated Id strings.
For your test data it looks like this:
arrUniques.JPG : https://imgur.com/QUzLx0s
Attachment 2246

Rem 3 Building up array of groupings
The end result from this code section will be to have a 2 Dimensional ( 2 column ) array of this approximate sort of form, taking as example your sample data…

arrEnucsIds() =
A News 15.11.2017 20 __ , __{ „20:19:12” , „20:19:32” , „20:49:12” , ….etc }
A News 15.11.2017 22 __ , __{ “22:26:58” , “22:27:18” …etc }
A NEWS 11.18.2017 23 __ , __{ “23:01:21 , “ …etc }
B NEWS 16.11.2017 20 __ , __{ ………
B NEWS 18.11.2017 21 __ , __{ …….
C NEWS 17.11.2017 22 __ , __{ …….
arrGrpTimes.JPG : https://imgur.com/TFBS56b
Attachment 2247

So you see we have a set of times, ( 6 sets for your data example ( Index numbers 0 1 2 3 4 5 ) ) , from which we can select our times randomly from the array of time values, { } , in the second column of arrGrpTimes( , 2). The first column in the array, arrGrpTimes( , 1) , tells us to which group the times belong.
( In my coding and description I might refer to the second column elements as the Id group times – The end effect of the coding at this point is that I have a set or group of times for each unique Id. Note that the unique Id array, arrEnucsIds() , is made from the input historical data, Sheet2, so if we do not find such an Id in the full Id list from Sheet1, arrOutId() , we have the case of your….”case there is no spot aired in Sheet2 on a specific channel, date and timeslot then New Time will be same as start time…”….. (code section 4b(ii) in coding ) )

Rem 4
We are effectively looping down the data rows in Sheet1 ,and filling in the extra column.
‘4b) The first thing to do is determine which ( if any ) of the ID groups the output data , sheet 1, row belongs to. Once ( If ) we have a set of times, we go into the process of randomly picking a time from that set of times…

___Rem 5 “Getting the random times bit”…
At this point in the code I make a temporary 1 dimensional array, arrTempTimes(). This is the set of times for the particular Id group. The individual elements of that array, arrTempTimes(), have this sort of form
arrTempTimes(0) = “20:19:12”
arrTempTimes(1) = ”20:19:32”
arrTempTimes(2) = “ ..
_.... etc.
……
_.....
arrTempTimes(N) = “…
( So that temporary array will be filled a total of 6 times , for your test data in your version 3 file).
If we can generate a random integer number from 0 to N then we can use that to get at a random element of that array.
Conveniently for us, VBA has a function close to what we want , Rnd(), ( https://docs.microsoft.com/en-us/off...p/rnd-function ). This gives us a random number less than 1 but greater than or equal to zero. So, if I have my maths correct, then something like this should give us the number we want
__ IntegerOf(Rnd()*(N+1)) = like 2 or 0 or 5 or 3 or N or 2 or … .. etc…

---

This coding is by far not the best, quickest or most professional. Because, I am not a professional, and I prefer to do a more longer but simpler to understand coding as I can follow it and modify it easier later. There is a lot of redundancy in the coding with variables used and filled that could probably be trimmed away. But such extra things make it easier to modify and debug later.
See how you get on with the routine.
I have not tested it thoroughly, I leave that to you.
Possibly you might want to modify it, and I can help probably. I will probably be on line again over the next few days at some time
I will be happy to explain anything in more detail.


Alan

---

Here the latest routine that I have done for you again:
http://www.excelfox.com/forum/showth...ll=1#post11146

Using your version 3 file test data, this is the typical results that you get:
http://www.excelfox.com/forum/showth...ll=1#post11147
( Note I got a duplicate time in that particular test code run )

Thanks for all the hard work.
I'll get back to you after the thorough testing of the code.

Thanks
Haider