Need some help counting and comparing. I have a file that contains a parent
id, child id, and child status. I would like to find out all of the parent
ids with the children of a particular status. Below are a couple scenerios...

1) parent A has 5 children and their status is all "IP" post on worksheet 2.
2) parent B has 5 children and 2 are "IP" and 3 are "NP" then do nothing.

Any help in how to do this?

Thanks,

Something like this should work:

ColA
a
b
c

ColB
1
2
3
4
5
6
7
8
9

Cell E1 is c

Cell F1 is
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")
The function is entered with Ctrl+Shift+Enter (not just enter)

HTH,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pablo" wrote:

Need some help counting and comparing. I have a file that contains a parent
id, child id, and child status. I would like to find out all of the parent
ids with the children of a particular status. Below are a couple scenerios...

1) parent A has 5 children and their status is all "IP" post on worksheet 2.
2) parent B has 5 children and 2 are "IP" and 3 are "NP" then do nothing.

Any help in how to do this?

Thanks,

Hi Ryan,

Not sure I am following this... Below is what I am thinking

Parent Id Child Id Status
123 ABC IP
123 DEF IP
123 GHI IP
** All children are good, list parent on worksheet 2

456 JKL IP
456 MNO NP
456 PQR IP
456 STU IP
** at least one child is not IP, do nothing

"ryguy7272" wrote:

Something like this should work:

ColA
a
b
c

ColB
1
2
3
4
5
6
7
8
9

Cell E1 is c

Cell F1 is
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")
The function is entered with Ctrl+Shift+Enter (not just enter)

HTH,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pablo" wrote:

Need some help counting and comparing. I have a file that contains a parent
id, child id, and child status. I would like to find out all of the parent
ids with the children of a particular status. Below are a couple scenerios...

1) parent A has 5 children and their status is all "IP" post on worksheet 2.
2) parent B has 5 children and 2 are "IP" and 3 are "NP" then do nothing.

Any help in how to do this?

Thanks,