Assistance with Excel Data

**CFR01** · 03-31-2011, 12:53 PM

Hi All,

My knowledge of excel is basic but I was wondering if anyone could provide some feedback with a problem. I'm working with a spreadsheet that contains questionnaire data that was originally scanned. When matching the answers on the questionnaire with the spreadsheet I found some errors (the scanner marked numbers that weren't checked off). I've been going through each survey one by one and then checking the row in excel to see what is correct and what needs to be erased but there are over 300 participants.

Is there a formula or function I can use that will find and display the questions answered (marked by "1")? And if so, is there a way I can tell it to make the ones marked incorrectly blank?

I'm sure I'm not explaining the problem correctly and I apologize. The vertical row is marked 1-258 and the ID's of the participants are displayed horizontally. For example if the first person only checked off one item and the spreadsheet shows multiple items checked off is there a formula I can use to ask it to make the incorrect items blank? Or a formula that will put a "1" in certain rows? That might be easier since I have the surveys and know which numbers need to be selected.

Any help would be appreciated!!

**day92** · 03-31-2011, 01:13 PM

CFR01 - welcome to the forum. If I understand you correctly you should be able to sort by "1" and mark those incorrect. If this does not solve your issue can you please upload some sample data so we can better assist you.

**CFR01** · 03-31-2011, 01:21 PM

Thank you for the quick response Day92. Sorry I didn't explain things very well. I attached an example to show my problem. Yes I can use the find function to find which questions were marked by each person but the problem is the excel results don't match the paper questionnaire. Our scanner may have incorrectly marked items that weren't selected by the person taking the questionnaire.

For example, Lets say person (ID 989) only checked off answer 1 and 10 but on the excel file it shows they checked off 1-10. I have no problem fixing this manually but there are over 300 participants and the select options are 1-258 for each person. A bit overwhelming!

**day92** · 03-31-2011, 01:52 PM

If they all have "1" how do we determine which ones were marked incorrectly?

**CFR01** · 03-31-2011, 01:57 PM

Sorry about that. I match the data in the excel spreadsheet with the surveys and look for inconsistencies between the two.

Each "1" represents the number checked off by the participant. When we scanned the surveys we expected the excel data to match but that wasn't the case. The scanner must have had lint or dust because it counts answers that don't appear on the hand marked surveys.