List Values On New Sheet If Criteria Is Met

[Nanogirl21]

I have data set up in the format below.


You will see that there are duplicate values in column B and some duplicate values in column C.


Is there a way to list the parent value (column B) when all child values (column C) related to that parent says “Chris”?


I’d like the list to be on a new sheet called COMPLETE. This sheet is not created yet and would need to be created each time. The main sheet that the information is in is called MATH.


CURRENT FORMAT

A	B	C
1232	APPLES	CHRIS
1233	APPLES	CHRIS
1234	APPLES	CHRIS
1235	PEARS	CHRIS
1236	ORANGES	BIANCA
1237	ORANGES	BIANCA
1238	APPLES	ANGELA
1239	BANANAS	CHRIS
1240	ORANGES	BIANCA
1241	GRAPES	CHRIS
1242	ORANGES	BIANCA
1243	GRAPES	CHRIS
1244	STRAWBERRIES	TONY
1245	ORANGES	BIANCA
1246	WATERMELON	JANE
1247	STRAWBERRIES	JERRY

<tbody>
</tbody>

RESULTS EXPECTED ON NEW SHEET

A

PEARS

GRAPES

BANANAS

<tbody>
</tbody>

In my example APPLES, ORANGES, STRAWBERRIES, or WATERMELON did not show on the new sheet becuase alll of the child values (column C) does not say "CHRIS" for the related parent values (Column B).

EXTRA: It would be great to have a version that creates a list on a new sheet without defining the child value (column C) I’m looking for. It would always list the parent value (column B) if the child values (column C) are the same regardless of what the text says.

RESULTS EXPECT FOR EXTRA VERSION

A

PEARS

GRAPES

BANANAS

ORANGES

WATERMELON

<tbody>
</tbody>

In this version ORANGES and WATERMELON was added to the list becuase all parent values (Column B) has the same child value (Column C).

 

[Roger Govier]

Hi

You need to use Advanced Filter.
I named your three columns on Math as Code, Type and Name
On sheet complete, enter Name in A1 and Type in A5
Then, add the following event code to your sheet complete

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
    Application.ScreenUpdating = False
    Call FilterData
    Application.ScreenUpdating = True
End Sub

Then Insert a code module and copy this code into it

Sub FilterData()
         Sheets("Math").UsedRange.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Complete").Range("A1:A2"), _
        CopyToRange:=Sheets("Complete").Range("A5"), _
        Unique:=True
End Sub

Remember to save your file as a .xlsm or a .xlsb file as it will now contain macros.

Now, if youenter Chris in A2 on sheet Complete, you will see a list of Apples, Pears, Bananas, Grapes.
If you delete any entry in A2 and press Enter you will see the complete unique list

 

[Nanogirl21]

Hi

You need to use Advanced Filter.
I named your three columns on Math as Code, Type and Name
On sheet complete, enter Name in A1 and Type in A5
Then, add the following event code to your sheet complete

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
    Application.ScreenUpdating = False
    Call FilterData
    Application.ScreenUpdating = True
End Sub

Then Insert a code module and copy this code into it

Sub FilterData()
         Sheets("Math").UsedRange.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Complete").Range("A1:A2"), _
        CopyToRange:=Sheets("Complete").Range("A5"), _
        Unique:=True
End Sub

Remember to save your file as a .xlsm or a .xlsb file as it will now contain macros.

Now, if youenter Chris in A2 on sheet Complete, you will see a list of Apples, Pears, Bananas, Grapes.
If you delete any entry in A2 and press Enter you will see the complete unique list

I tried this and it doesn't seem to be working. It's copying the entire worksheet from 1 to the other.

 

[Roger Govier]

It sounds as though you didn't enter the word Type in cell A5 of worksheet Complete

 

[Nanogirl21]

It sounds as though you didn't enter the word Type in cell A5 of worksheet Complete

Got it! I <g class="gr_ gr_91 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="91" data-gr-id="91">missunderstood</g> and actually entered the name and type of item I wanted to see. It worked perfectly when I entered the actual word "Type". Thank you for the help.