Array constant?

EmilyG · #1 (**permalink**) 12-21-04, 11:53

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)?

Thanks for your help!
Emily

shades · #2 (**permalink**) 12-21-04, 16:51

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.

shades · #3 (**permalink**) 12-21-04, 17:37

BTW there are alternatives that could be more efficient.

DavidCoutts · #4 (**permalink**) 12-22-04, 06:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech