Excel Consecutive Formula

[mahmed1]

Hi -

I have 3 columns of data that holds

Date, contact number and answered which will have Yes or No (Data is sorted)
Im trying to figure out how to look at the number and get the consecutive number of times the word Yes appears before there is a No
So find the No in answered column for that contact number and count how many times above that there is a Yes or put a 1 next to it

And then it will need to reset and look at the next batch for that contact number - I can have helper columns if that helps

Eg say i have this scenario



Date….Contact No…Answered…Result to show

01/01…..0789………….Yes

01/01…..0789………….Yes

01/01…..0789………….No…….1

01/01…..0789………….Yes……Reset here as it was answered so put a 2

01/01…..0789………….Yes

01/01…..0789………….No…….1

01/01…..0789………….No…….1

01/01…..0789………….No…….1

01/01…..0789………….Yes……Reset here as it was answered so put a 2

01/01…..0789………….No

01/01…..0789………….No

01/01…..0789………….No



No further answered calls so leave blank so essentially im trying to find all the answered where it shows yes and then put a 1 for all the No above it



Hope this makes sense



I tried this with no luck



=IF(B2<>B1,"",IF(C2="No","",IF(C2="Yes",IF(COUNTIF(INDEX($C$2:C2,MATCH(B2,$B$2:B2,0)):C2,"No")>0,2,1),"")))

So essentially how many No were there before a yes - put a 1 in the cell and 2 to denote it resets there ie answered at that point where it shows yes..

Result should be like screenshot

 

[dreid1011]

Will you know where the last row is per number?

 

[mahmed1]

Hey no i wont know where the last row will be

All im interested in show put a 1 at each occurence of a No where there is a Yes at some stage after it so if there is a yes - put a 1 at each occurence of No above it

If there is a yes followed by yes i ain’t interest in those 1s nor am i interested in the 1s that dont have a yes after the No

So essentially give me all the contacts that had a yes after a no and mark a 1 against the no and 2 at the point there is a yes

 

[dreid1011]

Hey no i wont know where the last row will be

All im interested in show put a 1 at each occurence of a No where there is a Yes at some stage after it so if there is a yes - put a 1 at each occurence of No above it

If there is a yes followed by yes i ain’t interest in those 1s nor am i interested in the 1s that dont have a yes after the No

So essentially give me all the contacts that had a yes after a no and mark a 1 against the no and 2 at the point there is a yes

Okay, I figured that was the case. I have it working down to the last No's without any more answers for that number.. Not sure how to handle those yet. I will keep at it though.

 

[dreid1011]

Okay, this is what I came up with:

The "COUNTIFS" looks up to 4 rows ahead of where the formula is to check if there are any more "Yes" results for the current number, if there are not, then it will return 0 and will not enter a 1 for those rows that have "No". The downside to this is you have to have some idea of the longest possible run of "No's" and adjust the "COUNTIFS" range to accommodate it. If the longest possible run could be 6 "No's" then you would change the ranges to be "B2:B7" and "C2:C7" accordingly. Let me know how that works for you.

Book1
	A	B	C	D
1	Date	Contact #	Answered	Result
2	1/1/2024	0789	Yes
3	1/1/2024	0789	Yes
4	1/1/2024	0789	No	1
5	1/1/2024	0789	No	1
6	1/1/2024	0789	No	1
7	1/1/2024	0789	Yes	2
8	1/1/2024	0789	Yes
9	1/1/2024	0789	No	1
10	1/1/2024	0789	Yes	2
11	1/1/2024	0789	Yes
12	1/1/2024	0789	Yes
13	1/1/2024	0789	Yes
14	1/1/2024	0789	No	1
15	1/1/2024	0789	Yes	2
16	1/1/2024	0789	Yes
17	1/1/2024	0789	No	1
18	1/1/2024	0789	No	1
19	1/1/2024	0789	No	1
20	1/1/2024	0789	Yes	2
21	1/1/2024	0789	Yes
22	1/1/2024	0789	No	1
23	1/1/2024	0789	No	1
24	1/1/2024	0789	Yes	2
25	1/1/2024	0789	No
26	1/1/2024	0789	No
27	1/1/2024	0788	Yes
28	1/1/2024	0788	No	1
29	1/1/2024	0788	Yes	2
Sheet6

Cell Formulas
Range	Formula
D2:D29	D2	=IF(B2<>B1,"",IF(AND(C2="Yes",C1="No"),2,IF(AND(COUNTIFS(B2:B6,B2,C2:C6,"Yes")>0,C2="No",B2=B3),1,"")))

 

[mahmed1]

Thank you - with the example above if you look at row 25,26,27
I would have expected to see 1,1,2 as there is a No, No, Yes

The other thing i need to account for is if the contact number changes then it needs to almost reset and do same thing for that contact number, not sure if the formula takes that into account

 

[mahmed1]

hey did you see my previous message

 

[mahmed1]

apologies- think it works as just saw the contact numbers were different

Really appreciate it

 

[mahmed1]

Hey it looks like it doesn’t work 100%

Look at example below - it looks like when numbers change it shows a blank so if there is a call that has no followed by a yes, it misses the 1st 1 but if numbers change and the i have a no, no and then yes it misses the first 1 but catches the 2nd no

 

[dreid1011]

The beginning IF statement was the cause for the first "No" on a number change to be ignored. Give this a try:

Book1
	A	B	C	D
1	Date	Contact #	Answered	Result
2	1/1/2024	0789	Yes
3	1/1/2024	0789	Yes
4	1/1/2024	0789	No	1
5	1/1/2024	0789	No	1
6	1/1/2024	0789	No	1
7	1/1/2024	0789	Yes	2
8	1/1/2024	0789	Yes
9	1/1/2024	0789	No	1
10	1/1/2024	0789	Yes	2
11	1/1/2024	0789	Yes
12	1/1/2024	0789	Yes
13	1/1/2024	0789	Yes
14	1/1/2024	0789	No	1
15	1/1/2024	0789	Yes	2
16	1/1/2024	0789	Yes
17	1/1/2024	0789	No	1
18	1/1/2024	0789	No	1
19	1/1/2024	0789	No	1
20	1/1/2024	0789	Yes	2
21	1/1/2024	0789	Yes
22	1/1/2024	0789	No	1
23	1/1/2024	0789	No	1
24	1/1/2024	0789	Yes	2
25	1/1/2024	0789	No
26	1/1/2024	0789	No
27	1/1/2024	1234	No	1
28	1/1/2024	1234	No	1
29	1/1/2024	1234	Yes	2
30	1/1/2024	1234	Yes
31	1/1/2024	1234	Yes
32	1/1/2024	1234	Yes
33	1/1/2024	1234	No
34	1/1/2024	1234	No
Sheet1

Cell Formulas
Range	Formula
D2:D34	D2	=IF(AND(C2="Yes",C1="No"),2,IF(AND(COUNTIFS(B2:B6,B2,C2:C6,"Yes")>0,C2="No",B2=B3),1,""))