Nov 26 2020 04:06 AM
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.
Please help..???
Nov 26 2020 04:43 AM
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.
Nov 26 2020 08:18 AM
What is the max number of values in each volumes A & B?
Nov 26 2020 09:27 AM - edited Nov 26 2020 09:28 AM
@Jihad Al-Jarady There is no limit except the maximum length of a cell value (32767 characters)