problem related to missing values

Iron Contributor

Hello Everyone,

If I want to check if the values of one column are included in the other column and if not then which values are missing, how can I do that?

Attached print screen for your reference. In the below I want to check if all of Column A values are included in Column B and if not then the missing values in Column D. I used the VLOOKUP formula but its giving me an error. Could you kindly check and assist.

 

ez.PNG

 

Please help..???

3 Replies

@Excel 

 

Here are two custom VBA functions:

 

Function AinB(ByVal A As String, ByVal B As String) As Boolean
    Dim p() As String
    Dim i As Long
    B = " | " & B & " | "
    p = Split(A, " | ")
    For i = 0 To UBound(p)
        If InStr(B, " | " & p(i) & " | ") = 0 Then
            AinB = False
            Exit Function
        End If
    Next i
    AinB = True
End Function

Function AnotinB(ByVal A As String, ByVal B As String) As String
    Dim p() As String
    Dim i As Long
    Dim r As String
    B = " | " & B & " | "
    p = Split(A, " | ")
    For i = 0 To UBound(p)
        If InStr(B, " | " & p(i) & " | ") = 0 Then
            r = r & " | " & p(i)
        End If
    Next i
    If r <> "" Then
        AnotinB = Mid(r, 4)
    End If
End Function

In C2:

=AinB(A2,B2)

In D2:

=AnotinB(A2,B2)

Fill down.

@Excel 

What is the max number of values in each volumes A & B?

@Jihad Al-Jarady There is no limit except the maximum length of a cell value (32767 characters)