Compare two lists with VBA

[rjh3m8]

Hi all,

I need to compare a list of response answers to a list of correct answers and mark which ones are correct (highlight the cells green) and which ones are wrong/extra/missing data (highlight red). Each question may have 2, 4, or 6 numbers as the answer, with each number in a separate column. Similarly, the responses will have each number in a separate column. The order of the response does not matter, but I need to know if all the correct numbers are present, with none missing and no extras added. For example, if the answer should include "5, 4, 6, 2" it is okay if they are in a different order, like "4, 2, 6, 5", as long as they are all present. However, I need to know if they miss a number or add extras like, "5, 4, 2" or "5, 4, 6, 3, 2".

So far I have been able to code something that looks to see if the two lists match exactly, but it doesn't allow the items to be in a different order. I thought about doing a sort first, but for a different scoring protocol it is necessary to keep the answers in the order the person responds.

I think I've uploaded a mini-sheet which shows a short example of what the item list (correct answers) will look like, and what a response list may look like. In reality, there are 54 rows of items.

rjh3m8_scorecompare.xlsx
	M
7
Sheet11

Any ideas? Thanks in advance!

 

[shknbk2]

Your example data didn't include anything. However, assuming you can get the two separate comma-separated strings to the function, it will return the values described in the comments. It should leave the original data untouched for the scoring protocol.

Sub PrintCompare()
    Dim i As Integer
    i = ListCompare("5, 4, 6, 2", "4, 2, 6, 5")
    Debug.Print i
End Sub

Function ListCompare(list1 As String, list2 As String) As Integer
    'Return values:
    ' 0 - lists are the same
    ' 1 - lists same size but different numbers
    ' 2 - list2 is smaller
    ' 3 - list2 is larger
    
    Dim array1 As Variant
    Dim array2 As Variant
    Dim i As Integer
    Dim equalValues As Boolean
    
    array1 = Split(Replace(list1, " ", ""), ",")
    array2 = Split(Replace(list2, " ", ""), ",")
    
    If UBound(array1) > UBound(array2) Then
        ListCompare = 2
    ElseIf UBound(array1) < UBound(array2) Then
        ListCompare = 3
    Else
        array1 = ConvertAndSortArrayAtoZ(array1)
        array2 = ConvertAndSortArrayAtoZ(array2)
        equalValues = True
        For i = LBound(array1) To UBound(array1)
            If array1(i) <> array2(i) Then
                equalValues = False
                Exit For
            End If
        Next
        If equalValues Then
            ListCompare = 0
        Else
            ListCompare = 1
        End If
    End If
End Function

Function ConvertAndSortArrayAtoZ(iArray As Variant)
    Dim i As Long
    Dim j As Long
    Dim Temp As Integer
    Dim myArray() As Integer
    
    'Change to integer array
    ReDim myArray(UBound(iArray))
    For i = LBound(iArray) To UBound(iArray)
        myArray(i) = iArray(i)
    Next i
    
    'Sort the Array A-Z
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
            If myArray(i) > myArray(j) Then
                Temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = Temp
            End If
        Next j
    Next i
    ConvertAndSortArrayAtoZ = myArray
End Function

 

[rjh3m8]

Your example data didn't include anything. However, assuming you can get the two separate comma-separated strings to the function, it will return the values described in the comments. It should leave the original data untouched for the scoring protocol.

Sub PrintCompare()
    Dim i As Integer
    i = ListCompare("5, 4, 6, 2", "4, 2, 6, 5")
    Debug.Print i
End Sub

Function ListCompare(list1 As String, list2 As String) As Integer
    'Return values:
    ' 0 - lists are the same
    ' 1 - lists same size but different numbers
    ' 2 - list2 is smaller
    ' 3 - list2 is larger
   
    Dim array1 As Variant
    Dim array2 As Variant
    Dim i As Integer
    Dim equalValues As Boolean
   
    array1 = Split(Replace(list1, " ", ""), ",")
    array2 = Split(Replace(list2, " ", ""), ",")
   
    If UBound(array1) > UBound(array2) Then
        ListCompare = 2
    ElseIf UBound(array1) < UBound(array2) Then
        ListCompare = 3
    Else
        array1 = ConvertAndSortArrayAtoZ(array1)
        array2 = ConvertAndSortArrayAtoZ(array2)
        equalValues = True
        For i = LBound(array1) To UBound(array1)
            If array1(i) <> array2(i) Then
                equalValues = False
                Exit For
            End If
        Next
        If equalValues Then
            ListCompare = 0
        Else
            ListCompare = 1
        End If
    End If
End Function

Function ConvertAndSortArrayAtoZ(iArray As Variant)
    Dim i As Long
    Dim j As Long
    Dim Temp As Integer
    Dim myArray() As Integer
   
    'Change to integer array
    ReDim myArray(UBound(iArray))
    For i = LBound(iArray) To UBound(iArray)
        myArray(i) = iArray(i)
    Next i
   
    'Sort the Array A-Z
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
            If myArray(i) > myArray(j) Then
                Temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = Temp
            End If
        Next j
    Next i
    ConvertAndSortArrayAtoZ = myArray
End Function

Hi, just to clarify, would I need to input each of the 54 lists at the beginning or with their own variable names? At: i = ListCompare("5, 4, 6, 2", "4, 2, 6, 5")
Also, is this automatically looking at a particular range to evaluate, or am I overlooking where it states that part?

 

[shknbk2]

would I need to input each of the 54 lists at the beginning or with their own variable names?

Your first post didn't include a full table; only a single blank cell. So, my code is merely an example of how 2 different lists would be compared.

is this automatically looking at a particular range to evaluate, or am I overlooking where it states that part?

The code will need to be updated to know where to get the groups of lists to be compared. Please provide some example data. When providing the example by XL2BB, make sure to click the Preview button at the top of the reply window to see how it would look after posting.

 

[rjh3m8]

I am having trouble getting the add-on to install (my computer isn't finding it), but I've attached a screenshot. Basically there's an item number in the first column, then the answer/response numbers are in each column following. The response COULD be on another sheet mirroring the answer items, if that would be simpler.

Thanks

 

[shknbk2]

The picture is good. Thanks. Can you now post a picture of what you would like the results to look like (where does the result go that determines if the numbers are the same or different)?