Counting given amount of consecutives

[Marvo]

Hi Again, I've constructed a formula

=XLOOKUP("W",K:K,B:B,,0,-1)

That works well, it finds the most recent time there was a "W" in a column.

How could I change that formula so that it would look for a given amount of consecutive "W"s please?

I'm thinking maybe of having it refer to a cell $F$5579 with a number in it, then look for that amount of "W"'s.

So if I put a 3 in the reference cell, the formula would then look for the most recent time there were 3 consecutive "W"'s in column K.

Would that be the way to go?

As always any help appreciated and if there's a better way of achieving the desired result I'm happy to be educated.


/?

 

[dermie_72]

=Countif(K:K, "W")

Will return how many W's are in the range.

 

[Marvo]

Thanks but I dont need the total, I need to find consecutives. As in, when was the last time we won 4 in a row.

 

[dermie_72]

us a formula to increment a number for each consecutive W, Then when you go back to 0 use another column to flag that number as the streak.
Assuming I've added 2 columns next to K which contains the W's
L2 =

=IF(K2="W",1,0)

L3 =

=IF(K3="W",L2+1,0)

M2 =

=IF(AND(L3=0,L2<>0),"Streak","")

Then you can filter by Streak.
Or you can set up a table to count the number of times a streak has occurred
# Consecutive wins
1

=COUNTIFS(L:L,R1,M:M,"Streak")

:Fill down
2
3
4
5

 

[Marvo]

Thanks. I'll give that a go, I already have a helper column where W=3, D=1 & L=0 so I could use that. I'll report back.

I've found a similar question by jchick90 posted Feb 22nd which has a different method, I might try to cannibalise that too, see which works best.

 

[Peter_SSs]

What should happen if the last long winning streak was actually 5 but you are looking for the last time you won 4 in a row? See my first example below.

A few small varied sets of sample data and expected results with XL2BB would help clarify what you have, what where it is and what you are trying to achieve if the following is not what you want.

Sounds like a helper column would be acceptable. Would a vba solution also be acceptable?

Pending some of the answers above, this could be a possible helper column method.
Note that in this first example the date reported actually ends a streak of 5 but that ends with a streak of 4 by my reckoning. Is that what you want?

24 03 18.xlsm
	B									K	L	M	N	O
1	Date									Result	Helper	Streak of interest	W/L/D Streak	Last time
2	1/01/2024									W	W1	4	W	20/01/2024
3	2/01/2024									L	L1
4	3/01/2024									L	L2
5	4/01/2024									W	W1
6	5/01/2024									W	W2
7	6/01/2024									W	W3
8	7/01/2024									W	W4
9	8/01/2024									L	L1
10	9/01/2024									L	L2
11	10/01/2024									L	L3
12	11/01/2024									W	W1
13	12/01/2024									W	W2
14	13/01/2024									L	L1
15	14/01/2024									L	L2
16	15/01/2024									L	L3
17	16/01/2024									W	W1
18	17/01/2024									W	W2
19	18/01/2024									W	W3
20	19/01/2024									W	W4
21	20/01/2024									W	W4
22	21/01/2024									D	D1
23	22/01/2024									D	D2
24	23/01/2024									W	W1
25	24/01/2024									W	W2
26	25/01/2024									W	W3
27	26/01/2024									L	L1
28	27/01/2024									L	L2
29	28/01/2024									D	D1
30	29/01/2024									L	L1
31	30/01/2024									L	L2
32
33
Streak

Cell Formulas
Range	Formula
O2	O2	=XLOOKUP(N2&M2,L2:L40,B2:B40,"N/A",,-1)
L2:L33	L2	=IF(K2="","",K2&IF(K1=K2,MIN(MID(L1,2,9)+1,M$2),1))

A couple of other examples.

24 03 18.xlsm
	B									K	L	M	N	O
1	Date									Result	Helper	Streak of interest	W/L/D Streak	Last time
2	1/01/2024									W	W1	4	L	N/A
3	2/01/2024									L	L1
4	3/01/2024									L	L2
5	4/01/2024									W	W1
6	5/01/2024									W	W2
7	6/01/2024									W	W3
8	7/01/2024									W	W4
9	8/01/2024									L	L1
10	9/01/2024									L	L2
11	10/01/2024									L	L3
12	11/01/2024									W	W1
13	12/01/2024									W	W2
14	13/01/2024									L	L1
15	14/01/2024									L	L2
16	15/01/2024									L	L3
17	16/01/2024									W	W1
18	17/01/2024									W	W2
19	18/01/2024									W	W3
20	19/01/2024									W	W4
21	20/01/2024									W	W4
22	21/01/2024									D	D1
23	22/01/2024									D	D2
24	23/01/2024									W	W1
25	24/01/2024									W	W2
26	25/01/2024									W	W3
27	26/01/2024									L	L1
28	27/01/2024									L	L2
29	28/01/2024									D	D1
30	29/01/2024									L	L1
31	30/01/2024									L	L2
32
Streak (2)

Cell Formulas
Range	Formula
O2	O2	=XLOOKUP(N2&M2,L2:L40,B2:B40,"N/A",,-1)
L2:L32	L2	=IF(K2="","",K2&IF(K1=K2,MIN(MID(L1,2,9)+1,M$2),1))

24 03 18.xlsm
	B									K	L	M	N	O
1	Date									Result	Helper	Streak of interest	W/L/D Streak	Last time
2	1/01/2024									W	W1	2	D	22/01/2024
3	2/01/2024									L	L1
4	3/01/2024									L	L2
5	4/01/2024									W	W1
6	5/01/2024									W	W2
7	6/01/2024									W	W2
8	7/01/2024									W	W2
9	8/01/2024									L	L1
10	9/01/2024									L	L2
11	10/01/2024									L	L2
12	11/01/2024									W	W1
13	12/01/2024									W	W2
14	13/01/2024									L	L1
15	14/01/2024									L	L2
16	15/01/2024									L	L2
17	16/01/2024									W	W1
18	17/01/2024									W	W2
19	18/01/2024									W	W2
20	19/01/2024									W	W2
21	20/01/2024									W	W2
22	21/01/2024									D	D1
23	22/01/2024									D	D2
24	23/01/2024									W	W1
25	24/01/2024									W	W2
26	25/01/2024									W	W2
27	26/01/2024									L	L1
28	27/01/2024									L	L2
29	28/01/2024									D	D1
30	29/01/2024									L	L1
31	30/01/2024									L	L2
32
Streak (3)

Cell Formulas
Range	Formula
O2	O2	=XLOOKUP(N2&M2,L2:L40,B2:B40,"N/A",,-1)
L2:L32	L2	=IF(K2="","",K2&IF(K1=K2,MIN(MID(L1,2,9)+1,M$2),1))

 

[Marvo]

I think VBA would be best, I've just started learning that. It seems to work through searches much quicker. Only know the basics though. Managed to do some sorts.

I've still got a lot to work through, will need criteria added to further searches. I cant get that XL2BB to work but I can upload the workbook to the cloud for sharing.

I usually ask one question, then try to apply the answer to several others.

Sorry for the tardy response, been incredibly busy this morning.

 

[Marvo]

Just to clarify, the question is, if a team was to draw their last 5 matches, when was the last time that happened? That could go back several years, even decades.

The criteria to be added would be is the game either a league game or all matches including cups.

Then a further criteria would be either home or away.

 

[Marvo]

So this is the search box I will be using. The plan is, I put a figure in one of the cells, in the image its a 7 in C25.
The formula or VBA would then be searching for the most recent time the team "W" (won) in C19, 7 C25, consecutive "H" (home) in B25, "LEAGUE" in A25 games.
The search for the "W" is on worksheet "ALL" column K.
The search for "League" is on worksheet "ALL" column J.
The search for the "H" is on worksheet "ALL" column E.

Each column is currently 5558 rows long.

Would it help if I shared the workbook on one drive?

 

[Marvo]

Sorry, the result would be the date (worksheet ALL Column B) to be entered in D25 with a hyperlink to the actual sequence.

This would only be required on a few occasions during the season so an update button would be much better than formula continually searching.