How can I tell the difference between two rows in Excel?
I created an assessment that applicants completed in Excel. I have a clue where I copy my answers and it tells me if their answers match my keywords. I would like to add a formula that will also show me the differences between the candidate cell (B2) and the key cell (A2) , so that it is easier to see if they are wrong.
I tried to use =SUBSTITUTE(B2,A2,"")
but it only gives me differences at the beginning or end of the line. Usually the difference is in the middle.
For example, my key (cell A2) might say: Cold War | Bay of Pigs | Fidel Castro
And applicant (cell B2) can say Cold War | Cuban Missile Crisis | Fidel Castro
I want this formula back: Cuban Missile Crisis
source to share
You can try something like this ...
Function CompareStrings(keyRng As Range, ansRng As Range) As String
Dim arr() As String
Dim i As Long
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(keyRng.Value, arr(i)) = 0 Then
CompareStrings = arr(i)
Exit Function
End If
Next i
End Function
Then you can use this UDF like below ...
=CompareStrings(A2,B2)
If you want to also compare them in reverse order and return a non-matching string from any of them, try this ...
Function CompareStrings(ByVal keyRng As Range, ByVal ansRng As Range) As String
Dim arr() As String
Dim i As Long
Dim found As Boolean
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(keyRng.Value, Trim(arr(i))) = 0 Then
found = True
CompareStrings = arr(i)
Exit Function
End If
Next i
If Not found Then
arr() = Split(keyRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(ansRng.Value, Trim(arr(i))) = 0 Then
CompareStrings = arr(i)
Exit Function
End If
Next i
End If
End Function
Use this as before as shown below ...
=CompareStrings(A2,B2)
So the function will first compare all parts of the string B2 to A2 and if it finds any mismatch it will return that part of the string and if it doesn't find any mismatch then it will compare all the parts of the string in A2 to B2 and return any part line mismatches. Therefore, he will compare both paths.
source to share