Comparing two sheets to establish if data has been entered in corresponding rows

I have recorded answers from same participants to two questionnaires. The questionnaires are the exact same questions except one was administered before training and one after to establish any change in opinion. The before results are sheet1 and the after are sheet2. The rows in each sheet represent the answers from one participant i.e. row1 on sheet1 and 2 is the same participant.

What i am looking for is a way to identify which participants completed both questionnaires and which completed one or the other.

I also have a column with notes so if they were absent for one this information is available. Would it be possible to show the notes next to the counts?

I hope this is clear and thank you for any help in advance.

Do you have the names of the participants on both sheets? If so, which columns are used to hold these?

Pete

Hi Pete,
Thanks for your reply. I don't have their names as the questionnaires were annonymous but each corresponding row on each sheet is the same participant.

I could add in a column with a unique reference if this is required. It would be column 1.

Thanks again.

It would help if you attached a sample Excel workbook (the FAQ describes how to), as it is difficult to guess exactly how your data is laid out.

One way of doing this, assuming that your data is numbers, and is in columns A to M, with notes in column N, is to put this in cell O1 of Sheet2:

=IF(SUM(Sheet1!A1:M1)=0,"Absent in Sheet1 - "&Sheet1!N1,IF(SUM(A1:M1)=0,"Absent in Sheet2 - "&N1,"Present for both"))

then copy this down.

Hope this helps.

Pete