+ Reply to Thread
Results 1 to 4 of 4

Surveys file - Identifying missing responses

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    6

    Surveys file - Identifying missing responses

    Hi,

    I have a spreadsheet that contains weekly surveys for a number of people. I collect all responses over time and I am trying to determine who hasnít sent the latest survey response.
    In the attached file, there is a surveyors tab with all the people surveyed.
    What I would like to get is a list, like the one filtered in the data tab, that shows the surveyor's names that havenít sent the responses, per question, for the latest date, in this case 7/16.

    Sometimes suveyors might send only responses to question1 but they might still miss question2.

    I tried to build a pivot table to show the ones missing but didnít get too far aheadÖ

    Any suggestions / ideas?

    Thanks again for all the help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Surveys file - Identifying missing responses

    Hi

    Try this.

    F1: TEST (this is just a heading)
    F2: =COUNTIFS($B$2:$B$39,B2,$C$2:$C$39,C2) Copy down to F39.
    Put filter onto the range A:F
    Column B filter on 7/16/12
    Column F filter on 1

    This will give a list of all people that have not sent in 2 responses for July 16.

    It doesn't show anyone that didn't respond at all.

    Does that help?

    rylo

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Surveys file - Identifying missing responses

    not really... that formula is just showing who hasnt replied to both question1 and question 2 for 2 times. Pedro, for example, replied to question1 both on 7/9 and 7/16. What I am interested in knowing is the people that replied on 7/9 that havent replied yet on 7/16, per question.

    This would be that output:

    Alessandro Question1 Not on 7/16
    Felipe Question1 Not on 7/16
    Luiz Question1 Not on 7/16
    Gustav Question2 Not on 7/16
    Thais Question2 Not on 7/16

    Pedro is not part of the list as it doesnt matter if he just replies to question1, as long as it is both on 7/9 and 7/16.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Surveys file - Identifying missing responses

    Hi

    Next try.

    1) Make sure that your items in column B are really dates. At the moment, in the example file, they are text.
    2) I1: =MAX(B:B)
    3) E2: =IF(COUNTIFS(A:A,A2,B:B,$I$1,C:C,C2),"","Not On "&TEXT($I$1,"dd/mm")) Copy down.

    I think you will have to change the date structure as I am using dd/mm/yy format, not the mm/dd/yy that you are using.

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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