# problem related to missing values

Regular Contributor

# problem related to missing values

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.

3 Replies

# Re: problem related to missing values

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.

# Re: problem related to missing values

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

# Re: problem related to missing values

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