Avatar of Cartillo
Cartillo
Flag for Malaysia asked on

Copy Data at the variety time

Hi Experts,

How to make the attached script able to copy booking (title) in a different time slot at the order sheet so that each day the "title" will be appearing at a different time? The time should be between Start/End Time at cell C9&E9(Booking sheet)


Sub spreaddatatest()
Dim start_date As Date, start_time As Date
Dim end_date As Date, end_time As Date
Dim booking_date As Date, booking_time As Date
Dim duration As Date, gap As Date
Dim number_of_days As Integer
Dim frequency As Integer
Dim booking As Integer, booking_column As Integer, booking_row As Integer
Dim last_booking_row As Integer
Dim end_time_column As Integer
Dim prevent_double_bookings As Boolean
Dim result

With Sheets("Booking")
    '-- initialise
    start_date = CDate(.[c7])
    end_date = CDate(.[e7])
    start_time = CDate(.[c9])
    end_time = CDate(.[e9])
    prevent_double_bookings = True
    
    '-- before 6 o'clock = next day
    If start_time < TimeValue("06:00:00") Then start_time = start_time + 1
    If end_time < TimeValue("06:00:00") Then end_time = end_time + 1
    If end_time < start_time Then MsgBox ("Starting time is higher that ending time"): End
       
    '-- calculate time span
    number_of_days = end_date - start_date + 1
    duration = (end_time - start_time) * number_of_days - 0.0001
    frequency = .[c11]
    If frequency > 1 Then gap = duration / frequency
End With

booking_date = start_date
booking_time = start_time
last_booking_row = Sheets("Order").Range("A:A").Find(what:=end_time - WorksheetFunction.Floor(end_time, 1), lookat:=xlWhole).Row
end_time_column = Sheets("Order").Range("2:2").Find(end_date).Column
loop_process_bookings:
For booking = 1 To frequency
    
    '-- locate booking position
    booking_column = Sheets("Order").Range("2:2").Find(booking_date).Column
    booking_row = (booking_time - TimeValue("6:00:00")) * 24 * 6 + 3
    
    '-- prevent double bookings where possible
    While prevent_double_bookings = True And Sheets("Order").Cells(booking_row, booking_column) <> ""
        booking_row = booking_row + 1
        If booking_row > last_booking_row Then
            booking_column = booking_column + 1
            booking_row = Sheets("order").Range("A:A").Find(what:=start_time, lookat:=xlWhole).Row
        End If
        '-- when booking extends end date, evenly disperse remaining bookings over complete interval
        If booking_column > end_time_column Then
                booking_date = start_date
                booking_time = start_time
                frequency = frequency - booking + 1
                gap = duration / frequency
                booking = 1
                prevent_double_bookings = False
                GoTo loop_process_bookings
        End If
    Wend
   
    '-- add booking to orders
    Sheets("Order").Cells(booking_row, booking_column) = Sheets("Order").Cells(booking_row, booking_column) & vbCrLf & Sheets("Booking").[c5]
    '-- determine next booking
    booking_time = booking_time + gap
    While booking_time > end_time
        booking_date = booking_date + 1
        booking_time = booking_time - end_time + start_time
    Wend
Next booking

End Sub

Open in new window

OrderList-V3.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Cartillo

8/22/2022 - Mon
macksm

Use arandom number generator and switch required time slots
Cartillo

ASKER
Hi,

How to incorporate this method in this script, hope Experts could help.
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Norie

Carlos

Can you clarify exacty what you want?

You have a booking over 4 days between 01-Jan and 4-Jan with a frequency of 40.

To begin with, what does frequency mean?

Does it mean that there will be 40 entrys on the order sheeet with each entry being for a 10-minute period?

Also do you want to evenly spread the frequency over the dates?

In this case 10 for each day.

Finally periods on different days shouldn't clash.

For example if 1-Jan 6:20-6:30 is the first period, that can't be repeated on 2,3,4 Jan.

Sorry for all the questions but I think it might be able to simplify this a little.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Cartillo

ASKER
Hi imnorie,

>Does it mean that there will be 40 entrys on the order sheeet with each entry being for a 10-minute period?

Yes, you're right

>Also do you want to evenly spread the frequency over the dates?
exactly, that's what I intended to do

>Finally periods on different days shouldn't clash.
Yes, this to prevent the same "Title" being repeated on the same time.

Hope I have unanswered all your question.
 




Saqib Husain

Hi, did you try mine?
Cartillo

ASKER
Hi ssaqibh,

Thanks for the superb idea, so far this is the only approachable method able dispersed the data evenly within the given condition. However, I noticed few cells are not fully utilized, attached the workbook. Those cells I have highlighted in "yellow." Is that any chance to make use this cell also, so that its balance with other cells comparatively?
Here's the gap that I used for:
Title AAA = 0
Title BBB = 1
Title CCC= 2
Title DDD= 3
OrderList-V5.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cartillo

ASKER
Hi ssaqibh,

Hope my request is possible to  achieve. Can we make the gap factor switch automatically from 0,1 and 2 regardless of data uniformity (similar method that you have suggested earlier in another question with a check-box).
Norie

I'm sorry but I must be missing something here.

It's just not possible to have a different time on each day with the example data.

There are only 22 slots available each day and the average frequency per day is 13.

ie 13 slots are needed per day

So if 13 slots are taken on the first day, there are only 9 slots available on the 2nd day if they aren't
to clash with the first day.

Is there some other rule or criteria I've missed here?
Cartillo

ASKER
Hi imnorie,


The objective is to make use the empty slots as the maximum that we could.  We can allow a same data copied at different dates on same time if that is the only option we have. Sorry, this bit confusing.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Norie

Are blocks of slots allowed?

eg from 0610 to 0630 - a block of three 10 minute slots.
Cartillo

ASKER
Hi imnorie,

Yes, it's allowed.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cartillo

ASKER
Hi imnorie,

Thanks for the code. You’re right. I think ssaqibh's approach with a gap factor provider better data cluster. The only limitation that I have now is to segregate the data evenly when we have a situation like I described in “ID: 36362248”. Hope ssaqibh will consider this request.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cartillo

ASKER
Hi,

I noticed the offset copied data outside of start/end time, e.g.
Date: 1-2Jan
Time: 06:00 – 13:00
Frequency: 70
Gap Factor:1
Offset:2

The data were copied up to  13:10. Make changes at offside effect end time? By right offside adjustment should maintain within timeframe.  
Saqib Husain

This should be done on a trial basis. If you have such densely populated data then try using a high gap factor, maybe 5 or even 10 then apply the offsets.
Cartillo

ASKER
Hi,

Thanks for the great help, its really make a lot of difference in my booking style.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23