problem related to missing values

%3CLINGO-SUB%20id%3D%22lingo-sub-1933872%22%20slang%3D%22en-US%22%3Eproblem%20related%20to%20missing%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1933872%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EIf%20I%20want%20to%20check%20if%20the%20values%20of%20one%20column%20are%20included%20in%20the%20other%20column%20and%20if%20not%20then%20which%20values%20are%20missing%2C%20how%20can%20I%20do%20that%3F%3C%2FP%3E%3CP%3EAttached%20print%20screen%20for%20your%20reference.%20In%20the%20below%20I%20want%20to%20check%20if%20all%20of%20Column%20A%20values%20are%20included%20in%20Column%20B%26nbsp%3Band%20if%20not%20then%20the%20missing%20values%20in%20Column%20D.%20I%20used%20the%20VLOOKUP%20formula%20but%20its%20giving%20me%20an%20error.%20Could%20you%20kindly%20check%20and%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ez.PNG%22%20style%3D%22width%3A%20815px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236257iF6F489257D2A72B7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ez.PNG%22%20alt%3D%22ez.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1933872%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934085%22%20slang%3D%22en-US%22%3ERe%3A%20problem%20related%20to%20missing%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20two%20custom%20VBA%20functions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20AinB(ByVal%20A%20As%20String%2C%20ByVal%20B%20As%20String)%20As%20Boolean%0A%20%20%20%20Dim%20p()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20B%20%3D%20%22%20%7C%20%22%20%26amp%3B%20B%20%26amp%3B%20%22%20%7C%20%22%0A%20%20%20%20p%20%3D%20Split(A%2C%20%22%20%7C%20%22)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(p)%0A%20%20%20%20%20%20%20%20If%20InStr(B%2C%20%22%20%7C%20%22%20%26amp%3B%20p(i)%20%26amp%3B%20%22%20%7C%20%22)%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20AinB%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Function%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20AinB%20%3D%20True%0AEnd%20Function%0A%0AFunction%20AnotinB(ByVal%20A%20As%20String%2C%20ByVal%20B%20As%20String)%20As%20String%0A%20%20%20%20Dim%20p()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20r%20As%20String%0A%20%20%20%20B%20%3D%20%22%20%7C%20%22%20%26amp%3B%20B%20%26amp%3B%20%22%20%7C%20%22%0A%20%20%20%20p%20%3D%20Split(A%2C%20%22%20%7C%20%22)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(p)%0A%20%20%20%20%20%20%20%20If%20InStr(B%2C%20%22%20%7C%20%22%20%26amp%3B%20p(i)%20%26amp%3B%20%22%20%7C%20%22)%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20r%20%26amp%3B%20%22%20%7C%20%22%20%26amp%3B%20p(i)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20If%20r%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20AnotinB%20%3D%20Mid(r%2C%204)%0A%20%20%20%20End%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20C2%3A%3C%2FP%3E%0A%3CP%3E%3DAinB(A2%2CB2)%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%3C%2FP%3E%0A%3CP%3E%3DAnotinB(A2%2CB2)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@Zan_Hanifee 

 

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.

@Zan_Hanifee 

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)