1. Registered User
Join Date
Oct 2004
Posts
5

Hello, all.

I'm wondering if there's any way to store multiple values in the same cell, so that I can test single values in other cells to see if they exist in the multiple value cell.

Here's the sitch...

Some multiple choice exam questions have multiple acceptable answers. So in the answer key, I'd like to be able to say that the answer to exam question number 1 is {"b","d"}, meaning that either b or d is acceptable. When looking at a student's answer to that question, I'd like to be able to compare it to a single cell containing both answers, and return "TRUE" if it matches one of the answers.

Any ideas? Am I dreaming in my desire to do this without resorting to VBA code (I'm trying to get this thing to run FAST)?

Emily

2. Registered User
Join Date
Oct 2003
Posts
1,091
One possible way is to use an IF statment with OR, like this:

=IF(OR(B3="a",B3="b",B3="c",B3="f"),"TRUE","False" )

BTW, VBA can be very fast, if written correctly.

3. Registered User
Join Date
Oct 2003
Posts
1,091
BTW there are alternatives that could be more efficient.

4. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
If you pick your range properly and test on that range in 1 go then VBA can be very fast so for example

Code:
```Sub Test()
Dim Lastcell As Range
Set Lastcell = Range("B1").EntireColumn.Find("*", , , , , xlPrevious)

If Not Lastcell Is Nothing Then
With Range(Cells(2, 1), Cells(Lastcell.Row, 1))
.FormulaR1C1 = "=IF(OR(RC[1]=""a"",RC[1]=""b"",RC[1]=""c"",RC[1]=""f""),""TRUE"",""False"")"
.Formula = .Value
End With
End If

Set Lastcell = Nothing
End Sub```
which uses shades formula from above runs in approx 1 second

Dave

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•