Counting and Comparing
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, |
Counting and Comparing
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, |
Counting and Comparing
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, |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com