Compare multiple rows for duplicated values in individual columns

[LyndonMBA]

I've put together an algorithm to compare students' responses (i.e. compare
one students' responses against all other students). The workbook accomodates
8 individual multiple choice test worksheets +8 individual multiple choice
scoring worksheets +8 individual multiple choice grading worksheets [+ 8
Short Answer sheets in the same breakdown]. My problem is that even though I
can get the code to work, after I copy the algorithm into four or five sheets
the file gets so big that my Dell Vostro (2.2 GHz w/ 4 GB RAM) fails and
excel crashes. I'm basically writing an if statement: IF(cell=other cell,1,0)
but duplicated 100 times (for 100 multiple choice questions) and for 30
students against 30 students X 12 classes: 100X30X30X12 - it's big on one
worksheet; then I try to copy it onto 8 more. The objective here is to
determine whether students are copying off each other...

 

[LyndonMBA]

I've put together an algorithm to compare students' responses (i.e. compare
one students' responses against all other students). The workbook accomodates
8 individual multiple choice test worksheets +8 individual multiple choice
scoring worksheets +8 individual multiple choice grading worksheets [+ 8
Short Answer sheets in the same breakdown]. My problem is that even though I
can get the code to work, after I copy the algorithm into four or five sheets
the file gets so big that my Dell Vostro (2.2 GHz w/ 4 GB RAM) fails and
excel crashes. I'm basically writing an if statement: IF(cell=other cell,1,0)
but duplicated 100 times (for 100 multiple choice questions) and for 30
students against 30 students X 12 classes: 100X30X30X12 - it's big on one
worksheet; then I try to copy it onto 8 more. The objective here is to
determine whether students are copying off each other...

Some more information, I'd also like to display the data as a correlation
chart (mission accomplished) - but excel crashes whenever I attempt to copy
the worksheet into other sheets. As it stands, I've got a workbook with
multiple sheets doing all of the grading - everything is color coded via
conditional formatting (i.e. 100% (4.0) is green with 0-50% (0.0) colored
red; correct responses on the multiple choice tests green, incorrect red and
etc.) - so the workbook without the data correlation of students' responses
weighs in at 20 MB. When I add the algorithms for the student multiple choice
correlation on one sheet, the workbook jumps to over 50MB. After copying into
the 5th sheet: 114 MB.

Lastly, I've tried mapping the MC responses to a range data-base A-Z
matching to Real Numbers and ran the correl(ranges) [saves MB but inaccurate
- look at the statistics definition of correlation and you'll see why].

Again - the objective is to test for students cheating...

Any suggestions???