+ Reply to Thread
Results 1 to 27 of 27

Manual Sheet to monitor Arrival and Departure patterns for categories

  1. #1
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Manual Sheet to monitor Arrival and Departure patterns for categories

    Hi, Im drafting out a manual sheet to monitor arrival and departure per category and I'm having problems with the formula (Row 23-28). I have manually stated the correct answers in Row17-22 just to compare if formula answers are correct.

    Screenshot an actual file attached just in case

    A friend recommended me to just split the category on different rows instead as a work around (Eg. JENNY will be Row 3 for A, Row 4 for D, Row 5 for H) and have additional rows to add each type per category. But i just thought to check with you guys if you can recommend a solution.

    Appreciate it. Thank you.

    PS: in addition, the sheet will have more names/rows added in time.


    also posted via:
    excelguru.ca/forums/showthread.php?8994-Manual-Sheet-to-monitor-Arrival-and-Departure-patterns-for-categories&p=36969#post36969
    mrexcel.com/forum/excel-questions/1054584-manual-sheet-monitor-arrival-departure-patterns-categories.html
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Zhang018; 05-08-2018 at 04:01 AM. Reason: Update Attachment

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Why not attach the file here rather than on a hosting site?

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    BSB

  3. #3
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    oh okay, i tried it when i posted, but the screen just froze. so i thought i wasnt allowed to do it being new to the forum. IN any case, attached already. will update the body message now

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    A VBA-based solution: output in "Report".

    My results do not always agree with yours.

    Please Login or Register  to view this content.
    If this works, it will need refining to meet your actual data format (I am sure!).
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    yeah results don't match so cant use that..

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Please explain how you get 4 Cat 2 departures on Jan 11? (column N)

    On Jan 10 there are 5"D"s so 5 in Cat 2 (Column M)

    on Jan 11 There are 2 "D"s so to my mind there are 3 Departures not 4

  7. #7
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Please explain how you get 4 Cat 2 departures on Jan 11? (column N) -- Jenny, Mike, Abraham, and Harry are moving to Cat 3, so they all count as a Cat 2 Departure

    On Jan 10 there are 5"D"s so 5 in Cat 2 (Column M) - Bert is counted as a Cat 2 Arrival

    on Jan 11 There are 2 "D"s so to my mind there are 3 Departures not 4 - Jan 11 indeed has 2 "D"s which are under Amy (a Cat 2 arrival) and Bert (a Cat 2 Stayover). Jan 11 Cat 2 Departures as listed on the 1st question..

    although not yet solved, i appreciate you taking the time to review it and helping me find a solution..

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Removed by JT
    Last edited by JohnTopley; 05-07-2018 at 10:41 AM.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  10. #10
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    updated, been trying to edit my post but the site is restricting me saying im not allowed to post links yet

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Can you confirm (or not) the for Jan 11th there should be 4 Cat 3 arrivals: rows 3,4 5 and 1 not 3 as your results.

    and Jan 10th, 3 Cat 2 departures: rows 6,8 and 9.

  12. #12
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Can you confirm (or not) the for Jan 11th there should be 4 Cat 3 arrivals - Yes there are Four Cat 3 Arrivals on JAN11, Rows 3, 4, 5, and 12 (Jenny, Mike, Abraham, Harry). Thank you for that, will update the manual count sheet.

    and Jan 10th, 3 Cat 2 departures: rows 6,8 and 9. - Correct.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    sorry, so its not possible to use regular excel formulas? coz i dont know how to use macro
    Last edited by Zhang018; 05-08-2018 at 05:13 AM.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    It might be but the formula will be complex: hopefully someone will respond with a formula.

  16. #16
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    thank you, still very much appreciate your help.. trying to study your macros configuration now..
    Last edited by Zhang018; 05-08-2018 at 06:44 AM.

  17. #17
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Can i check, tab SHEET1 is not important? I only need to look into tab TEST and tab REPORT?

    still trying to replicate your Macro configuration to 5 Categories, each with 4 types

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Correct: I kept sheet1 so I could check my results.

    If you need further help, then post again.

  19. #19
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    im actually having a hard time to understand how the codes were done via Macro. Im accessing the Vba config, most of which i am unable to breakdown and figure out. if its not too much trouble, would you have an online resource for me to study based on the method that you have used? Coz now instead of A-H, i need to do A-P, and instead of 3 Categories, it would now be 5. The new classification would be:

    Cat 1 - A, B, C, D
    Cat 2 - E, F, G, H
    Cat 3 - I, J
    Cat 4 - K, L
    Cat 5 - M, N O, P

    I assumed "For n = 14 To 14" will be retained as is? Plus still unsure how you did the fcode = 64, and V1, V2, V3 numbers. Was attempting to replicate it, but not successful.
    Attached Files Attached Files
    Last edited by Zhang018; 05-11-2018 at 07:01 AM. Reason: Update

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    See the attached.
    It has a named range "Categories" in A14:A18 of "Report"

    A new function to determine the category number

    Please Login or Register  to view this content.
    Amended code:

    Please Login or Register  to view this content.
    Changes highlighted in RED

    I tested with the original categories and results were correct.

    P.S. Missed your new file but I'll leave you to test!

    As for learning VBA: I suggest you look at what books are available: books by John Walkenbach are of excellent quality.
    Attached Files Attached Files
    Last edited by JohnTopley; 05-11-2018 at 07:24 AM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Added a new line to check for empty column (end of data)

    Please Login or Register  to view this content.
    Update the code in last file posted

  22. #22
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    wow, the macro configuration you made works really well. Thank you very much for your time and help sir.

    Will try to research more about John Walkenbach as you have mentioned earlier, coz Ive no idea how to create that..

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  24. #24
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    wow, the macro configuration you made works really well. Thank you very much for your time and help sir.

    Will try to research more about John Walkenbach as you have mentioned earlier. At the moment, I have no idea how to draft that kind of solution..

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  26. #26
    Registered User
    Join Date
    05-05-2018
    Location
    Bangkok
    MS-Off Ver
    2007
    Posts
    13

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    it's a workaround using macro, but not an excel formula. Should i still mark this as solved?

  27. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Manual Sheet to monitor Arrival and Departure patterns for categories

    Unless it is not solved (!), please mark as solved.

    I don't think you will get a formula solution anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-17-2017, 10:36 PM
  2. Train live departure display monitor
    By lunatikus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2013, 08:10 PM
  3. Replies: 2
    Last Post: 05-30-2013, 12:45 PM
  4. [SOLVED] Formula to count number of nights falling in each month from arrival and departure dates
    By zicitron in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-28-2013, 02:16 AM
  5. Replies: 0
    Last Post: 04-10-2013, 09:39 AM
  6. [SOLVED] Calculating the number of people in building based on arrival and departure times
    By dvs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2012, 09:45 AM
  7. Function needed to calculate arrival patterns
    By klwinston in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2006, 09:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1