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,