Surveys file - Identifying missing responses

[grillodaniel]

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.

[rylo]

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

[grillodaniel]

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.

[rylo]

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