Exclude the ID's that do not follow a sequence-CodePudding

I have a table with different stages of different ID's.

I need to include only those IR's that are in phase 1, or phase 1 and 2, or phase 1,2 and 3, or phase 1,2,3 and 4. Col C in the attached table is the desired output. *** ID 1 is included, because it has Stage 1 and 2. ID2 has stage 1,2,3 and 4, hence included. If there are any stages that does not include all the prior stages, then that needs to be excluded, hence ID3 and 4 are excluded because they have stage 2,3,4 and 4 only respectively

CodePudding user response：

Interesting problem. Could be a stretch, but what about:

Formula in C2:

=IF(IFERROR(FIND(CONCAT(SORT(UNIQUE(FILTER(B$2:B$21,A$2:A$21=A2)))),"1234"),0)=1,"Y","N")

CodePudding user response：

For those that do not have Office 365.

Use SUMPRODUCT(COUNTIFS()):

=IF(SUMPRODUCT(
     COUNTIFS([ID],[@ID],[Stage],ROW($ZX$1:INDEX($ZX:$ZX,AGGREGATE(14,7,[Stage]/([ID]=[@ID]),1))))/
     (COUNTIFS([ID],[@ID],[Stage],ROW($ZX$1:INDEX($ZX:$ZX,AGGREGATE(14,7,[Stage]/([ID]=[@ID]),1)))) 
     (COUNTIFS([ID],[@ID],[Stage],ROW($ZX$1:INDEX($ZX:$ZX,AGGREGATE(14,7,[Stage]/([ID]=[@ID]),1))))=0)))=
     AGGREGATE(14,7,[Stage]/([ID]=[@ID]),1),"Y","N")

Another Office 365 Formula that works similar to above:

=IF(COUNT(UNIQUE(FILTER([Stage],[ID]=[@ID])))=MAXIFS([Stage],[ID],[@ID]),"Y","N")