Total non consecutive repeats 1

[cheryl27284]

This may be a hard one.

I have a sheet that has employee ID's,metrics and months. I want to count how many times an employee repeated the same thing but not month after month. For example, If employee 25 had "C3" for Jan and then again in Feb I don't want it to count. But if the employee had "C3" Jan, Feb, March and then June, I want it to count it as 2 repeats. Here is some sample data:

empiD Jan Feb March April May June
40020 C3 C3 C3 CFDQ C3 C3

Jan and Feb don't count becuase they are consecutive, Jan and March count as 1 repeat then March to May count as another repeat so that is 2 repeats that are not consecutive.

The purpose is to see if there is any improvement month over month.

 

[Gavona]

Are you specifically looking for C3 or do you want to count non-consecutive instances of C4 if that repeats as well?



Gavin

 

[Gavona]

You could do this:

empiD	Jan	Feb	Mar	Apr		Repeats	Jan	Feb	Mar	Apr
40020	c3	c3	c4	c3		2	1	0	0	1

Formula for Jan is:
=IF(AND(B5="C3",B5<>A5),1,0)
this copies across for the following months.

Hope this helps you get started


Gavin

 

[cheryl27284]

Hey thanks a lot, but it's not working every time. The below is coming up as 1 repeat and it should be 2. The first repeat was in June and then another in Aug. H8 does ="c3" and H8 <> G8 but its not populating a 1. The formula is this:

=IF(AND(H8="C3",H8<>G8),1,0


F G H I J
April May June July Aug
C3 C3 C3

 

[cheryl27284]

woops, I'm wrong it does work!