Thread: Identifying records based on conditions involving column values

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!

Hi HK,

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

=IF(SUMPRODUCT(--($A$2:$A$10=A2)*($B$2:$B$10<>B2)*($C$2:$C$10<>C2))>0,"Y","N")

Sample workbook is also attached herewith.

Regards,
DILIPandey

<click on below 'star' if this helps>

you are welcome hk..

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>