Identifying records based on conditions involving column values

[hk106]

Here is some sample data:

ID-------Group-----Status
01-------353--------Open
01-------532--------Closed
01-------532--------Hold
02-------216--------Open
02-------216--------Closed
03-------480--------Open
03-------480--------Closed
03-------634--------Closed
03-------689--------Verify

I'm looking to identify the records in which the ID contains at least one Group with an "Open" status and at least one different group with a status other than "Open". So I would want to see:

ID-------Group-----Status----Keep
01-------353--------Open---------Y
01-------532--------Closed--------Y
01-------532--------Hold----------Y
02-------216--------Open---------N
02-------216--------Closed-------N
03-------480--------Open---------Y
03-------480--------Closed-------Y
03-------634--------Closed-------Y
03-------689--------Verify--------Y

Please let me know if there are any questions on this. I would greatly appreciate help on this!

[dilipandey]

Hi HK,

You can use the following formula to achieve the desired results:-

Please Login or Register  to view this content.

Sample workbook is also attached herewith.

Regards,
DILIPandey

<click on below 'star' if this helps>

[hk106]

Dilipandey,

This is exactly what I was looking for. Thank you!

[dilipandey]

you are welcome hk..

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>