Check if parent row has corresponding values?

Blue1971

New Member
Joined
May 19, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I have a table in an Excel 2016 spreadsheet that has hierarchical rows:

undefined


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

Example:
undefined


Is there a way to do this in Excel 2016?

Thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hello Blue1971 & welcome to Mr. Excel

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

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hello Blue1971 & welcome to Mr. Excel

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

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top