+ Reply to Thread
Results 1 to 8 of 8

List of visits, I need to check if at least 1 visit was made in afternon and weekend

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Office 2010
    Posts
    4

    Unhappy List of visits, I need to check if at least 1 visit was made in afternon and weekend

    Hello my newly acquired friends,

    I have a list of household visits conducted by our workers. I'm using Excel 2010. Database is huge, has about 9000 rows and 150 columns. At the end I created several summary columns that give me a better and easier overview. I used codes, conditional formating with traffic light colors etc. Now one of the things I need to check is whether at least one visit for each address was conducted in the afternoon and weekend. My list of visits looks like this:

    Visits.jpg

    I was trying hard to create a neat formula on the same sheet, but I couldn't figure out anything. Now I created a separate sheet where I first check individually for each visit (1 to 20) whether it was in the afternoon and at the end do the final check. And than again a similar thing for the weekends. It works, but it's ugly and awkward, plus significantly increases my file size. Currently it works like this:

    Afternoon weekend.jpg

    At the end I link my findings back to the original sheet and than color-code them.

    Check.jpg

    I as I said, it does work somehow, but I'm not satisfied with my solution. Excel is all-mighty, I'm quite positive that there must a be a shorter, easier and more elegant solution.

    Any help will be greatly appreciated.

    Have a nice day,
    Masha

  2. #2
    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: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    Can you please post a small sample file showing expected results.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    Hi there... and welcome to the Excel Forum. You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely (certainly in this case!!). Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    07-06-2015
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    Hi again! Thank your for your kindness and willingness to help. I posted screenshots exactly in order to avoid revealing too many confidential data. I guess now I know also for the future that xls example files always work better and generate more feedback. Here it is:

    Visits example.xlsx

  5. #5
    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: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    See attached: "Visits" columns AE and AG: your results in AF & AH for comparison
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-06-2015
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    That's amazing! And so neat. Thank you VERY MUCH!

    My next task now is to understand how this work, which means I'll dig deep into Google University. :D

  7. #7
    Registered User
    Join Date
    07-06-2015
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    It seems that I get the sumproduct part, but how do the two minuses in front of the hour/weekday work? I've noticed, that by removing one, the formula switches from counting 1 to counting -1, and by removing also the second one, it goes from -1 to true/false. Why is that so?

  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: List of visits, I need to check if at least 1 visit was made in afternon and weekend

    Yes: the "--" converts the TRUE/FALSE to 1/0 so that SUMPRODUCT can work on numeric values.

+ 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. [SOLVED] Count the number of visits by visit types.
    By Marvin85 in forum Excel General
    Replies: 8
    Last Post: 10-02-2014, 05:19 PM
  2. [SOLVED] Generate a list number of visits at different times in a day
    By TPS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2014, 11:16 PM
  3. [SOLVED] Repeat Names on a list depending on how many visits
    By Dave69rock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2013, 04:17 PM
  4. Replies: 5
    Last Post: 04-09-2010, 07:44 AM
  5. how to check if user has made a selection on the sheet
    By chawla12amit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2005, 03:51 PM

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