+ Reply to Thread
Results 1 to 6 of 6

Return dates that match a single criteria (a countif criteria)

  1. #1
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Return dates that match a single criteria (a countif criteria)

    I have a football listing of players in Col A and dates on rows along the top. Each player has to assign a Y or a N on their named row to suggest whether or not they are able to attend that day.
    Currently I have a macro (courtesy of John Davis) that returns the dates that everyone can do. I want to amend this macro slightly though on the basis that if not everybody can attend (ie at least one person says N) then the macro will return the dates where the majority of people can come, and return the names of the players that are missing.

    See attachment for what I have so far (B17:C19 are my expected results. If you change anything in the Y and N ranges these expected results will be cleared, just so you know.)
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-14-2010
    Location
    Scotland
    MS-Off Ver
    MS Office 2013
    Posts
    35

    Re: Return dates that match a single criteria (a countif criteria)

    nickmax1,

    Add this macro to your workbook and run. Hopefully this should do the trick if I have understood you correctly.

    Please Login or Register  to view this content.
    Thanks

    Stu

  3. #3
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Return dates that match a single criteria (a countif criteria)

    Stuart,
    Thanks for responding so fast!
    Its not quite what i was after though. What yours does (brilliantly though) is take all the dates and names of players that cant make it and lists them one after the other (though not in order strangely?)

    What I was hoping for was that of my list of dates the macro would use some sort of countif formula to identify the dates (or date) that has the least amount of N's and return just those dates along with the players names who would not be present.
    So for example if there were three days where only two players were not present the result would be:

    Wednesday 15/2/2014 Tom L,Stuart D
    Monday 1/3/2014 Nick C, Bob W

  4. #4
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Return dates that match a single criteria (a countif criteria)

    is this not possible?

  5. #5
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Return dates that match a single criteria (a countif criteria)

    I have updated the sheet with a countif formula and a conditional format to identify the colomns with the least amount of Ns. I was hoping someone clever enough could write code that could identify those colomns and return the days and dates beneath them (highlighted in red in the attached)
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Return dates that match a single criteria (a countif criteria)

    come on guys you are all a smart bunch!

+ 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: 9
    Last Post: 06-25-2013, 09:00 AM
  2. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 AM
  3. Countif with single column but criteria
    By asdvender in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-20-2008, 11:33 PM
  4. [SOLVED] Match Single Numeric Criteria and Return Multiple Numeric Labels
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2005, 04:10 PM
  5. Match Single Numeric Criteria and Return Multiple Numeric Labels
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-29-2005, 04:50 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