Identify ID’s with 2 or more instances of an event

[Kazlik]

I am looking for the most efficient way to do the following.

I have up to 10 different CodeID's that can be associated one or many times to an ID. I need to identify ID's that have 2 or more instances of the same CodeID. In the sample below ID 1 should be identified as it has two instances of CodeID 50.

ID CodeID
1 123
1 24
1 50
1 102
1 24
1 18
2 50
2 123
2 24

 

[RonB1111]

Did you mean to say 2 instances of 24 (not 50)? If so, enter this in C2 and copy down, then do an AutoFilter on Col C to exclude 1's:

=COUNTIFS(A:A,A2,B:B,B2)

 

[Kazlik]

Sorry about that, yes I meant 24. I would like to do this in PowerPivot and have it working using the below but suspect there is an easier way.

=IF ( CALCULATE(DISTINCTCOUNT(Sheet1[CodeID]),FILTER(Sheet1,Sheet1[ID]=EARLIER(Sheet1[ID]))) <> CALCULATE(COUNT(Sheet1[CodeID]),FILTER(Sheet1,Sheet1[ID]=EARLIER(Sheet1[ID]))) ,"yes", "no")

 

[scottsen]

I might have written it slightly different, based on style prefs, but functionally, I would have done the same. I can't think of an easier way.