Identify Pattern in a grid

[excel1404]

Hello Gurus,

Hoping to get some solution to the issue below.
In the production facility that I am working on, we have thousands of these working shift patterns and it is just getting challenging to understand what are the working patterns.

See below: Pattern 1 is simple and visually possible to see & understand: it says every 10th day - a recurrence happens (OFF on monday is a seperate 10th recurrence, OFF on Tuesday is another recurrence (Therefore technically, different type).

Pattern 2: Recurrence seems not possible. Therefore, Every 4th week the 1st week pattern recurs. Kind of a loop.

Pattern 3: seems to be similar as pattern 2. May be some deviations.

My challenge is to identify these and also then list them in another column what is the pattern for each of these types. If I am able to get this, then perhaps, I want to list that based on date.

[TABLE="width: 714"]
<tbody>[TR]
[TD]Working Shift[/TD]
[TD]Pattern Start date[/TD]
[TD]Week[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-01-01[/TD]
[TD]1[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-01-08[/TD]
[TD]2[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-01-15[/TD]
[TD]3[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-01-22[/TD]
[TD]4[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-01-29[/TD]
[TD]5[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-02-05[/TD]
[TD]6[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-02-12[/TD]
[TD]7[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-02-19[/TD]
[TD]8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-02-26[/TD]
[TD]9[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 1[/TD]
[TD]2018-03-05[/TD]
[TD]10[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[/TR]
[TR]
[TD]Pattern 2[/TD]
[TD]2018-01-01[/TD]
[TD]1[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 2[/TD]
[TD]2018-01-08[/TD]
[TD]2[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 2[/TD]
[TD]2018-01-15[/TD]
[TD]3[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 2[/TD]
[TD]2018-01-22[/TD]
[TD]4[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]N8[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-01-01[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-01-08[/TD]
[TD]2[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-01-15[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-01-22[/TD]
[TD]4[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-01-29[/TD]
[TD]5[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-02-05[/TD]
[TD]6[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-02-12[/TD]
[TD]7[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-02-19[/TD]
[TD]8[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-02-26[/TD]
[TD]9[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Pattern 3[/TD]
[TD]2018-03-05[/TD]
[TD]10[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]


Hope the expert Gurus can help here.

Thanks much.

Regards
excel1404

 

[SpillerBD]

If your sample Table is pasted to a workbook beginning at cell A1, an input cell for a Pattern is located at L1 and another input cell for the desired date, then the following array formula will pull the appropriate working pattern(?) from the field.

=INDEX($D$1:$J$25,MATCH(L1&(MOD(WEEKNUM(L2,2),10)),A1:A25&$C$1:$C$25,0),WEEKDAY(WEEKNUM(L2,2),2))

Use CTRL+SHIFT+ENTER to make as array formula.

 

[excel1404]

Thanks Spiller... It just went over the head on this friday evening here.... am not an advanced excel user... Perhaps, possible for you to indicate where I should put what in terms of an image? Thanks again.