how to identify number string/sequence on a row

[ampetkova]

Hello,
Capture.JPG
I am wondering how to set a formula in order to identify if a particular sequence exists on a row.

I am working with a large table which is consisting of employees' shifts.
Depending on the shifts combination I have different types of working cycles.
My purpose is to identify somehow a particular working cycle.

Shifts are marked as following: 1 -first, 2-second, 3 -night.

My point is to filter only employees who worked 6 days with the following sequence: 1 1 2 2 3 3
I have also sequences like 1st week: 1 1 1 1 1, 2nd week: 2 2 2 2 2; 3rd week: 3 3 3 3 3

I hope there is a way to do that.

Thank you for giving an advise!

[GeoffW283]

Welcome to the forum!

Let's see if this is close to what you want. With reference to my attached example workbook:

Duplicate your list of employees in rows below your current table. In the attached workbook this list starts at row-11.

Populate the following pattern detection formula into cell B11 and copy across and down:

Please Login or Register  to view this content.

Now create the following conditional formatting:
a) Select range B1:T5
b) Conditional formatting > New Rule > Use a formula to determine which cells to format
c) For the formula enter: =B11
d) Format as desired (I used yellow fill).

Now the start of the desired work pattern will be highlighted in your original table (A1:T5).

See the attached workbook which implements the above

[ampetkova]

Hi Geoff,

Yes, that definitely works!
I will not waste time anymore in manual work.

Thank you for your help

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[GeoffW283]

From ampetkova:

Just one quick question as I solved my thread already - If I want to unite your formula with more in order to add more combinations how can I do this?

Eg: =SUMPRODUCT(--(O10:T10={1,1,2,2,3,3}))=6, but here I want to add =SUMPRODUCT(--(O10:T10={1,2,2}))=3 and to receive true or false again

This is because sometimes the sequence 112233 could be only 122,233 etc, due to long annual sick or paid leaves etc.

Thanks in advance,
Ani

[GeoffW283]

Hi Ani,

You can add additional sequences to check for by enclosing them with an or() function. For your above example this would be (as before, in B11 copied across and down):

Please Login or Register  to view this content.

You can add more sequences in the same way to the same or() if you wish.

One note with reference to your example "--(O10:T10={1,2,2}))=3": If the sequence to look for has a length of 3 then the range within the formula also needs to have a length of 3, so your example would need to be: "--(O10:Q10={1,2,2}))=3"

Attached is a workbook that checks for 2 sequences.

Hope this works for you.

Geoff