# Consecutive occurrence based on current week

#### gd6noob

##### Board Regular
Hi guys,

I need some help with 2 formulas.

Column A - I would like a formula that counts for the past 13 weeks based on current week.
Column B - I would like to know if there are 4 or more consecutive occurrences , if possible, list the weeks of occurrences. If there is multiple occurrences, is it possible to list them all in 1 cell? (example, week 26-30 and week 32 to 35)

The value is a drop down list for user to pick but value does not matter, as long as theres a value.
 Past 13 weeks 4 Consecutive Week 23 Week 24 Week 25 Week 26 Week 27 Week 28 Week 29 Week 30 Week 31 Week 32 Week 33 Week 34 Week 35 Week 36 Week 37 Week 38 Week 39 Week 40 Week 41 Week 42 Week 43 Week 44 Shift A 50​ 50​ 50​ 50​ 50​ 50​ 50​ 50​ 50​ 50​ 50​ Shift B 60​ 60​ 60​ 60​ 60​ 60​ 60​ 60​ 60​ Shift C Shift D

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Eric W

##### MrExcel MVP
There are a few questions. When you say "past 13 weeks", do you mean Week 23-35, or 32-44? Are you going to add columns to the right (or left), and want the formula to adapt? What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15?

Here's a trial version. This goes from week 23 to 35. I added an empty column after the shift column to make it easier. It shows the start week of the streaks (there can only be 2 according to your rules), but not the length.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Past 13 weeks4 ConsecutiveWeek 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 38Week 39Week 40Week 41Week 42Week 43Week 44
211Week 26 Week 32Shift A5050505050505050505050
30 Shift B606060606060606060
40 Shift C
50 Shift D
Sheet16
Cell Formulas
RangeFormula
A2:A5A2=COUNTIF(E2:Q2,">0")
B2:B5B2=IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN(\$E2:\$N2)-COLUMN(\$E\$2),,4))=4),1)),"")&" "&IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN(\$E2:\$N2)-COLUMN(\$E\$2),,4))=4),2)),"")

#### gd6noob

##### Board Regular
There are a few questions.
When you say "past 13 weeks", do you mean Week 23-35, or 32-44? if current week is 35, then calculate week 22 to week 34. If current week is 37, calculate week 24 to week 36

Are you going to add columns to the right (or left), and want the formula to adapt? I will put this at the end of week 52 instead of at the beginning.

What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15? Not sure I understand this

Here's a trial version. This goes from week 23 to 35. I added an empty column after the shift column to make it easier. It shows the start week of the streaks (there can only be 2 according to your rules), but not the length.

#### jasonb75

##### Well-known Member
@gd6noob please don't type your replies inside of a quoted post, although you had the foresight to make it easier to see your comments by changing the font colour, it can still be confusing and it prevents others from quoting parts of your reply if they need to.

Referring back to part of Eric's post that you said you didn't understand
What do you want to do if the streak starts in the 13-week range, say at week 12, and goes into 12-15?

Lets assume that this week is week 41. This would mean that the previous 13 weeks would cover Week 28 to Week 40.
Using your example from post 1, what results would you expect to see for Shift A and Shift B?

#### Eric W

##### MrExcel MVP

I'm still hazy on some of your requirements, but here's how to move the formulas to the right.

Book1
1Week 1Week 22Week 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 52Current Week
2Shift A505050505050505050505011Week 26, Week 3236
3Shift B606060606060605Week 27Formula Range
4Shift C80808080805 Week 23 - Week 35
5Shift D0
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,\$BH\$2-14,1,13),">0")
BE2:BE5BE2=IFERROR(INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,\$BH\$2-14,,10))/(OFFSET(B2,0,\$BH\$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,\$BH\$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),1)),"")&IFERROR(", "&INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,\$BH\$2-14,,10))/(OFFSET(B2,0,\$BH\$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,\$BH\$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),2)),"")
BH4BH4="Week "&BH2-13&" - Week "&BH2-1

I didn't know how you intend to specify which week it is, so I just put the current week in BH2. I couldn't rely on just looking for the last used entry in the row, since it could be empty for the given week. I also assume that there are no entries past the current week, so the issue of a range extending past the last week won't come up.

#### gd6noob

##### Board Regular
@gd6noob please don't type your replies inside of a quoted post, although you had the foresight to make it easier to see your comments by changing the font colour, it can still be confusing and it prevents others from quoting parts of your reply if they need to.

Referring back to part of Eric's post that you said you didn't understand

Lets assume that this week is week 41. This would mean that the previous 13 weeks would cover Week 28 to Week 40.
Using your example from post 1, what results would you expect to see for Shift A and Shift B?
Sorry, didnt mean to confuse people.

And correct, so based on what today's current week and not include the current week.

#### gd6noob

##### Board Regular

I'm still hazy on some of your requirements, but here's how to move the formulas to the right.

Book1
1Week 1Week 22Week 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 52Current Week
2Shift A505050505050505050505011Week 26, Week 3236
3Shift B606060606060605Week 27Formula Range
4Shift C80808080805 Week 23 - Week 35
5Shift D0
Sheet3
Cell Formulas
RangeFormula
BD2:BD5BD2=COUNTIF(OFFSET(C2,0,\$BH\$2-14,1,13),">0")
BE2:BE5BE2=IFERROR(INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,\$BH\$2-14,,10))/(OFFSET(B2,0,\$BH\$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,\$BH\$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),1)),"")&IFERROR(", "&INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(OFFSET(C2,0,\$BH\$2-14,,10))/(OFFSET(B2,0,\$BH\$2-14,,10)="")/(SUBTOTAL(2,OFFSET(C2,0,\$BH\$2-14+{0,1,2,3,4,5,6,7,8,9},,4))=4),2)),"")
BH4BH4="Week "&BH2-13&" - Week "&BH2-1

I didn't know how you intend to specify which week it is, so I just put the current week in BH2. I couldn't rely on just looking for the last used entry in the row, since it could be empty for the given week. I also assume that there are no entries past the current week, so the issue of a range extending past the last week won't come up.
Appreciate the help, the formula for the first question works perfectly.

For the second question, maybe too difficult, how about if there are 4 consecutive data then can we have it say "Yes".
Is it possible to say how many time there was consective data. for example, using your sheet, Shift A had 2 occurrence of consective times, week 26 to week 30 and week 32 to week 35.

#### jasonb75

##### Well-known Member
And correct, so based on what today's current week and not include the current week.
Based on the current week being week 41, what results do you expect based on your example in post 1?

#### CA_Punit

##### Well-known Member
I tried to combine all the weeks which are consecutive for only Shift A

It is just a sample will require modification in ranges, if it is what you want i try to modify

Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Past 13 weeks4 ConsecutiveWeek 23Week 24Week 25Week 26Week 27Week 28Week 29Week 30Week 31Week 32Week 33Week 34Week 35Week 36Week 37Week 38Week 39Week 40Week 41Week 42Week 43Week 44
210Week 28Shift A11111111505050111111
30 Shift B606060606060606060
40 Shift C
50 Shift D
6
7
8
9Week 28//Week 32,Week 34//Week 37,Week 39//Week 43,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//,//
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=COUNTIF(E2:Q2,">0")
B2:B5B2=IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN(\$E2:\$N2)-COLUMN(\$E\$2),,4))=4),1)),"")&" "&IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(E1:N1)/(D2:M2="")/(SUBTOTAL(2,OFFSET(E2,0,COLUMN(\$E2:\$N2)-COLUMN(\$E\$2),,4))=4),2)),"")
E9E9=TEXTJOIN(",",FALSE,IFERROR(INDEX(\$E\$1:\$Z\$1,AGGREGATE(15,6,1/(1/(((IFERROR(INDEX(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),,1/(1/((COLUMN(A:Z)-1)))),-1)-AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)))<-1)*AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)))),COLUMN(A:Z))),"")&"//"&IFERROR(INDEX(E1:Z1,,IFERROR(INDEX(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),,AGGREGATE(15,6,1/(1/UNIQUE(IFERROR((((IFERROR(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),"")-INDEX(IFERROR(AGGREGATE(15,6,1/((E2:Z2<>"")*(COLUMN(E2:Z2)-COLUMN(E2)+1)^0*SUBTOTAL(102,OFFSET(INDEX(INDIRECT(CHAR(COLUMN(\$E:\$Z)+64)&2),,COLUMN(A:V)),,,,4))=4)*(COLUMN(E2:Z2)-COLUMN(E2)+1),(COLUMN(E2:Z2)-COLUMN(E2)+1)),0),,COLUMN(A:Z)+1))=-1)*COLUMN(A:Z)=0)*COLUMN(A:Z),""),TRUE)),COLUMN(A:Z)))+3,"")),""))

#### CA_Punit

##### Well-known Member
But your version of excel i will not be able to help as it doesn't have Textjoin function

Replies
3
Views
58
Replies
3
Views
52
Replies
0
Views
42
Replies
3
Views
94
Replies
7
Views
67