Check if parent row has corresponding values?

[Blue1971]

I have a table in an Excel 2016 spreadsheet that has hierarchical rows:

In column F, I want to flag child rows where the values in C, D, or E are missing from the parent row.

Example:

Is there a way to do this in Excel 2016?

Thanks.

 

[mse330]

Hello Blue1971 & welcome to Mr. Excel

Check the below macro, it should work if my understanding is correct … Let us know how it goes

Sub CheckParent()

Dim Txt$, Comma$, r&

For x = 2 To Range("A" & Rows.count).End(xlUp).Row
    If Cells(x, 2) <> "" Then
        If Not Columns("A").Find(Cells(x, 2).Value, LookAt:=xlWhole) Is Nothing Then
            r = Columns("A").Find(Cells(x, 2).Value, LookAt:=xlWhole).Row
            For i = 3 To 5
                If Range(Cells(r, 3), Cells(r, 5)).Find(Cells(x, i).Value, LookAt:=xlWhole) Is Nothing Then
                    Txt = Txt & Comma & Cells(x, i).Value
                    Comma = ","
                End If
            Next
             If Len(Txt) > 0 Then
                Cells(x, 6) = "ERROR [" & Txt & " missing from parent #" & Cells(r, 1) & "]"
                Cells(x, 6).Font.Color = vbRed
                Cells(x, 6).Characters(6, Len(Cells(x, 6)) - 5).Font.Italic = True
                Cells(x, 6).Font.Bold = True
            End If
        End If
    Txt = "": Comma = ""
    End If
Next

End Sub

 

[mikerickson]

You might be able to do this with a formula

In F3, put the formula =IF(SUMPRODUCT(COUNTIF(INDEX(C:E,MATCH(B3,A:A,0),0),C3:E3))<>COUNTA(C3:E3) , "Missing something", "all there")

It won't tell you which item is missing from the parent, but it will flag if something is missing from the parent

 

[Blue1971]

You might be able to do this with a formula

In F3, put the formula =IF(SUMPRODUCT(COUNTIF(INDEX(C:E,MATCH(B3,A:A,0),0),C3:E3))<>COUNTA(C3:E3) , "Missing something", "all there")

It won't tell you which item is missing from the parent, but it will flag if something is missing from the parent

Nailed it. Thanks.

 

[Blue1971]

Hello Blue1971 & welcome to Mr. Excel

Check the below macro, it should work if my understanding is correct … Let us know how it goes

Sub CheckParent()

Dim Txt$, Comma$, r&

For x = 2 To Range("A" & Rows.count).End(xlUp).Row
    If Cells(x, 2) <> "" Then
        If Not Columns("A").Find(Cells(x, 2).Value, LookAt:=xlWhole) Is Nothing Then
            r = Columns("A").Find(Cells(x, 2).Value, LookAt:=xlWhole).Row
            For i = 3 To 5
                If Range(Cells(r, 3), Cells(r, 5)).Find(Cells(x, i).Value, LookAt:=xlWhole) Is Nothing Then
                    Txt = Txt & Comma & Cells(x, i).Value
                    Comma = ","
                End If
            Next
             If Len(Txt) > 0 Then
                Cells(x, 6) = "ERROR [" & Txt & " missing from parent #" & Cells(r, 1) & "]"
                Cells(x, 6).Font.Color = vbRed
                Cells(x, 6).Characters(6, Len(Cells(x, 6)) - 5).Font.Italic = True
                Cells(x, 6).Font.Bold = True
            End If
        End If
    Txt = "": Comma = ""
    End If
Next

End Sub

This worked very well! Thanks!

It's handy to see which of the 'UseWith' values was the cause of the problem.

Cheers!

 

[Blue1971]

This worked very well! Thanks!

It's handy to see which of the 'UseWith' values was the cause of the problem.

Cheers!

 

[mse330]

Glad to help & thanks for reporting back