Announcement

Collapse
No announcement yet.

$100 USD for Combining Log Entries & Schedules onto a Verification Calendar Sheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • $100 USD for Combining Log Entries & Schedules onto a Verification Calendar Sheet



    Attached is my example spreadsheet for what I need. My actual project is nearly identical formatting; however, it encompasses somewhere on the order 500 Sequential Dates, 125 Personnel, 15000 Log Entries, and 18 Months of Schedules which are delineated into a few (e.g., 3) different sheets.

    The purpose of this project is to essentially loop through and populate the WorkCalendar sheet with a shift denotation (e.g., Days, Mids, etc.) in the appropriate row for that person.

    For example, the code should search LogEntries sheet and recognize that John assumed a "Days" shift on 1/17/2018 and on 1/19/2018.

    Then, it should replace the contents of cells C8 and E8 with "Days" on the WorkCalendar sheet.

    Next, it should repeat this process until it has looped through all of the entries on the LogEntries sheet.

    Furthermore, it should search Cycle_21 & Cycle_22 sheets to recognize that John was scheduled for a "Days" shift on 1/17/2018 and on 1/19/2018.
    --- Note: The start and end times for the LogEntries and the CycleSchedules are not exactly in sync with each other.
    --- However, they are within 2 hours of each other (e.g., LogEntries time of "05:30"/"17:30" should be considered equivalent to Cycle_21/22 time of "06:00"/"18:00" for a "Days"/"Mids" shift, respectively.)

    Then, it should replace the contents of cells C9 and E9 with "Days" on the WorkCalendar sheet.

    Finally, a formula should compare the contents of the LogEntries and the CycleSchedules portions for the WorkCalendar sheet (e.g., C8 compared to C9, D8 compared to D9, E8 to E9, etc.).
    --- This should return "YES" if they are the same or "NO" if they are different.
    --- If they are different, I would like the corresponding name and dates of those "NO" entries listed in the Differences sheet.

    I have standardized the formatting and nomenclature that I use for this purpose/workbook, so the code should be easily translatable to my actual project.

    Also, I am familiar enough with VBA coding to work my around; but, I am not familiar enough for this to not be an outrageously daunting time devotion for my newbie level of experience.

    Please forgive this lengthy introduction; however, I wanted to provide you with as much information as possible so that you understood the task. Thanks, in advance, for your help!



    Attached Files

  • #2
    I can look at this for you
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Comment


    • #3
      KjBox,

      Excellent. Once you have a feel for it, let me know what else you need (and/or how you're feeling with regard to a time frame). Thanks!

      Comment


      • #4
        It is midnight for me right now, but I have had a look through your file and will start working on a solution first thing in the morning. I should have a working solution within 48 hours.

        I assume the Calendar without the shift denotations is manually created. Is it created after manually checking that each personnel is in both schedule and log, and always for a 4 day period?

        I ask this because it should be possible to not only populate the calendar with code, but to create it at the same time according to the start date entered in cell B1 and the number of days to be considered is entered in cell B4.
        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

        Comment


        • #5
          Thanks for the reply! That timeline is acceptable for my needs.

          The WorkCalendar is created manually; if you could automate that, it would be great as well. For my actual project, I cross-checked using a formula to verify all personnel were in both Schedule & Log. Then, I created it line-by-line.

          Comment


          • #6
            With 125 personnel manually creating the calendar must take ages!
            We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

            Comment


            • #7
              Your sample file has a start date of Jan 17 2018 and end date of Jan 20 2019 with 4 days to be considered.

              Should the end date be Jan 20 2018?
              We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

              Comment


              • #8


                Payment received, many thanks.

                File sent by e-mail.
                We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                Comment

                Working...
                X