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
arrUniques.jpg
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
arrGrpTimes.jpg
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 )
Reply With Quote
Bookmarks