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

[nickmax1]

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.)

[stuart010]

nickmax1,

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

Public Sub GetUnavailable()

Application.ScreenUpdating = False

Dim intPlayers As Integer: Dim intDays As Integer: Dim intUnavail As Integer
For intPlayers = 6 To ThisWorkbook.Sheets("Sheet1").Cells(6, 1).End(xlDown).Row 'Row 6 to Last Player Name
    For intDays = 2 To 7 'Column B to G
        If ThisWorkbook.Sheets("Sheet1").Cells(intPlayers, intDays).Value = "N" Then
            intUnavail = ThisWorkbook.Sheets("Sheet1").Cells(65000, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets("Sheet1").Cells(intUnavail, 1).Value = ThisWorkbook.Sheets("Sheet1").Cells(5, intDays).Value
            ThisWorkbook.Sheets("Sheet1").Cells(intUnavail, 2).Value = ThisWorkbook.Sheets("Sheet1").Cells(intPlayers, 1).Value
        End If
    Next intDays
Next intPlayers

Application.ScreenUpdating = True

End Sub

Thanks

Stu

[nickmax1]

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

[nickmax1]

is this not possible?

[nickmax1]

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)

[nickmax1]

come on guys you are all a smart bunch!