Establishing a streak of non-numerical values.

[HockeyBoi]

Hey team,

I'm trying to establish a Win, Loss, or Tie streak for sports games.

In the example below, I'd like the "Streak" coloum to automatically generate the streak based on the values in subsequent columns. For instance, Team 1 would have a streak of W4, Team two would have a streak of T1, and then W1, L1, L2, W4...so on and so forth.

I've tried all sorts of variations but can't get it to perform properly. I could easily do it manually...but automation is more fun until this stumped me.

Any ideas?

Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
1		W	W	W	W
2		W	L	W	T
3		L	T	L	W
4		W	L	T	L
5		L	T	L	L
6		W	W	W	W
7		W	W	W	L
8		L	T	W	L
9		T	L	L	L
10		L	L	L	W

 

[Cubist]

If I understand, you're counting winning streak. For any other streak, replace "W" with the corresponding letter. Try:

Book1
	A	B	C	D	E	F	G
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	4	W	W	W	W
3	2	1	W	L	W	T
4	3	1	L	T	L	W
5	4	1	W	L	T	L
6	5	0	L	T	L	L
7	6	4	W	W	W	W
8	7	3	W	W	W	L
9	8	1	L	T	W	L
10	9	0	T	L	L	L
11	10	1	L	L	L	W
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=MAX(SCAN(0,C2:F2,LAMBDA(a,b,IF(b="W",a+1,0))))

 

[Phuoc]

...

 

[Eric W]

Another option:

Book1
	A	B	C	D	E	F	G
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	4W	W	W	W	W
3	2	1T	W	L	W	T
4	3	1W	L	T	L	W
5	4	1L	W	L	T	L
6	5	2L	L	T	L	L
7	6	4W	W	W	W	W
8	7	1L	W	W	W	L
9	8	1L	L	T	W	L
10	9	3L	T	L	L	L
11	10	1W	L	L	L	W
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LET(s,CONCAT(C2:H2),c,SEQUENCE(LEN(s)),r,RIGHT(s),m,MAX(IF(MID(s,c,1)<>r,c)),LEN(s)-m&r)

This finds the current active streak.

 

[Cubist]

It appears I've misunderstood the OP. Second attempt.

Book1
	A	B	C	D	E	F	G
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	W4	W	W	W	W
3	2	T1	W	L	W	T
4	3	W1	L	T	L	W
5	4	L1	W	L	T	L
6	5	L2	L	T	L	L
7	6	W4	W	W	W	W
8	7	L1	W	W	W	L
9	8	L1	L	T	W	L
10	9	L3	T	L	L	L
11	10	W1	L	L	L	W
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=F2&MAX(SCAN(0,C2:F2,LAMBDA(a,b,IF(b=F2,a+1,0))))

 

[Phuoc]

Or:

=LET(s,CONCAT(C2:H2),c,SEQUENCE(LEN(s)),r,RIGHT(s),MAX(IF(REPT(r,c)=RIGHT(s,c),c))&r)

 

[HockeyBoi]

Another option:

Book1
	A	B	C	D	E	F	G
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	4W	W	W	W	W
3	2	1T	W	L	W	T
4	3	1W	L	T	L	W
5	4	1L	W	L	T	L
6	5	2L	L	T	L	L
7	6	4W	W	W	W	W
8	7	1L	W	W	W	L
9	8	1L	L	T	W	L
10	9	3L	T	L	L	L
11	10	1W	L	L	L	W
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LET(s,CONCAT(C2:H2),c,SEQUENCE(LEN(s)),r,RIGHT(s),m,MAX(IF(MID(s,c,1)<>r,c)),LEN(s)-m&r)

This finds the current active streak.

This seemed to work really well in Office 365. Thank you so much!

Would you know how to get similar results in Excel 2016 for Mac?

 

[Eric W]

Would you know how to get similar results in Excel 2016 for Mac?

To be perfectly honest, I'm not sure. I don't know what functions are available to Excel 2016 for Mac, the online documentation is sparse. But I did come up with a formula using older functions that might work.

Book1
	A	B	C	D	E	F	G	H
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	4W	W	W	W	W
3	2	1T	W	L	W	T
4	3	2W	L	T	L	W	W
5	4	1L	W	L	T	L
6	5	2L	L	T	L	L
7	6	4W	W	W	W	W
8	7	1L	W	W	W	L
9	8	1L	L	T	W	L
10	9	3L	T	L	L	L
11	10	1W	L	L	L	W
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=(COUNTA(C2:H2)-MAX((COLUMN(C2:H2)-COLUMN(C2)+1)*(C2:H2<>LOOKUP(2,1/(C2:H2<>""),C2:H2))*(C2:H2<>"")))&LOOKUP(2,1/(C2:H2<>""),C2:H2)
Press CTRL+SHIFT+ENTER to enter array formulas.

You'll probably need to use Control+Shift+Enter when entering the formula in the formula bar.

 

[HockeyBoi]

To be perfectly honest, I'm not sure. I don't know what functions are available to Excel 2016 for Mac, the online documentation is sparse. But I did come up with a formula using older functions that might work.

Book1
	A	B	C	D	E	F	G	H
1	Team	Streak	Game 1	Game 2	Game 3	Game 4	Game 5
2	1	4W	W	W	W	W
3	2	1T	W	L	W	T
4	3	2W	L	T	L	W	W
5	4	1L	W	L	T	L
6	5	2L	L	T	L	L
7	6	4W	W	W	W	W
8	7	1L	W	W	W	L
9	8	1L	L	T	W	L
10	9	3L	T	L	L	L
11	10	1W	L	L	L	W
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=(COUNTA(C2:H2)-MAX((COLUMN(C2:H2)-COLUMN(C2)+1)*(C2:H2<>LOOKUP(2,1/(C2:H2<>""),C2:H2))*(C2:H2<>"")))&LOOKUP(2,1/(C2:H2<>""),C2:H2)
Press CTRL+SHIFT+ENTER to enter array formulas.

You'll probably need to use Control+Shift+Enter when entering the formula in the formula bar.

You're a wizard - cheers for that!