Grouping parts of one CELL with contents within another CELL

Here is an Excel example of what I need to solve preferably using a VBA Code.

Cell A1 contains Z002,Z003,Z004,Z006,Z007

Cell B1 Contains Z200

Cell C1 Contains Z002,Z003,Z004,Z005,Z006,Z007,Z008

B1 is a single value defining the values A1

All values in Cell A1 must be contained in Cell C1 for the result to be true

Cell D1 result would then be, Z200,Z005,Z008

To take that to the next row where it is not true

Cell A2 contains Z002,Z003,Z004,Z006,Z007

Cell B2 Contains Z200

Cell C2 Contains Z002,Z003,Z005,Z006,Z007

Cell D2 Result would be Z002,Z003,Z005,Z006,Z007

In this 2nd example all the values in A2 are not contained within C2 so the result in D2 would just be C2 again.

I want a formula in column D that refers to the values in A, B & C to get the desired result. The formula in D# can be a Macro as such, =Group(A#,B#,C#) So D returns the changes if A is part of the group in C, else just return C

Thanks in Advance

mbart67

I tried this,

Function Group(valuesA As String, valuesB As String, valuesC As String) As String
    Dim arrA() As String
    Dim arrB() As String
    Dim arrC() As String
    Dim Unmatched As String
    Dim i As Integer, Found As Boolean
    
    ' Split each string by commas into arrays
    arrA = Split(valuesA, ",")
    arrB = Split(valuesB, ",")
    arrC = Split(valuesC, ",")
    
    ' Check if all values in A are contained in C
    For i = LBound(arrA) To UBound(arrA)
        Found = False
        Dim j As Integer
        For j = LBound(arrC) To UBound(arrC)
            If Trim(arrA(i)) = Trim(arrC(j)) Then
                Found = True
                Exit For
            End If
        Next j
        ' If a value in A is not found in C, return the entire C list
        If Not Found Then
            Group = valuesC
            Exit Function
        End If
    Next i
    
    ' If all values in A are found in C, combine unmatched B and C
    Unmatched = valuesB
    
    ' Check for values in C that are not in A
    For i = LBound(arrC) To UBound(arrC)
        Found = False
        For j = LBound(arrA) To UBound(arrA)
            If Trim(arrC(i)) = Trim(arrA(j)) Then
                Found = True
                Exit For
            End If
        Next j
        ' If not found in A, add to the unmatched list
        If Not Found Then
            If Len(Unmatched) > 0 Then
                Unmatched = Unmatched & "," & arrC(i)
            Else
                Unmatched = arrC(i)
            End If
        End If
    Next i
    
    Group = Unmatched
End Function

VBA solution:

Function CheckValues(A As String, B As String, C As String) As String
    Dim arrA() As String
    Dim arrC() As String
    Dim result As String
    Dim allContained As Boolean
    Dim i As Integer, j As Integer
    Dim found As Boolean
    
    arrA = Split(A, ",")
    arrC = Split(C, ",")
    allContained = True
    
    ' Check if all values in A are contained in C
    For i = LBound(arrA) To UBound(arrA)
        found = False
        For j = LBound(arrC) To UBound(arrC)
            If arrA(i) = arrC(j) Then
                found = True
                Exit For
            End If
        Next j
        If Not found Then
            allContained = False
            Exit For
        End If
    Next i
    
    If allContained Then
        ' If all values in A are contained in C, find values not in A
        result = B
        For j = LBound(arrC) To UBound(arrC)
            found = False
            For i = LBound(arrA) To UBound(arrA)
                If arrC(j) = arrA(i) Then
                    found = True
                    Exit For
                End If
            Next i
            If Not found Then
                result = result & "," & arrC(j)
            End If
        Next j
    Else
        ' If not all values in A are contained in C, return C
        result = C
    End If
    
    CheckValues = result
End Function