Looking to see if a value appears consecutively for 4 weeks (and the count)...

[gazwilliamson]

Hi there,

Looking to see if someone can help with the following.

Column A is a NAME
Column B is a date value called Week Beginning (rounds it up to the Monday of the week (i.e. 10/02/20)

What I need to find is if the NAME has appeared consecutively for the last 4 weeks.

I had a "rough" idea, and have created 4 columns called Week 1 (C), Week 2 (D), Week 3 (E), Week 4 (F). These work out if the date in Column B is equal to one of last 4 weeks, and inserts TRUE.

Ideally, I would like to add column G, which would calculate if it A appears consecutively over the four weeks i.e insert YES, and then I could create a pivot table from this to analyse the volumes per week.

This is as far as I have got - I have a rough feeling it may need to be an IF AND statement, but that's about as far as I have gotten haha

Just to recap, here is the full list of columns

Column A = NAME
Column B = WEEK BEGINNING
Column C = Week 1
Column D = Week 2
Column E = Week 3
Column F = Week 4
Column G = hopefully the formula

Any help/guidance would be appreciated.

 

[etaf]

do you just need to see if TRUE is in all 4 columns C,D,E,F
But it looks to me like those columns will only have one filled for a row because of the WeekBeginning
So i assume you are looking to see if NAME exists and each column Exists
Is that the case
So any rows may have name and TRUE in
You just need for the NAME to also have 4 rows with a TRUE in ech column C,D,E,F

 

[gazwilliamson]

Yep - you're correct!