Results 1 to 4 of 4

Thread: Array constant?

  1. #1
    Join Date
    Oct 2004
    Posts
    5

    Unanswered: Array constant?

    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

  2. #2
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    BTW there are alternatives that could be more efficient.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    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
  •