Help with Current and highest streaks

[Telman86]

Hi there,

I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a solution to my problem and can't find exactly what I'm looking for, so apologies if this has already been asked in the near past.

I am looking to calculate the longest and current streaks of Wins and Losses for my pool team players. The problem is that players may not play every week, but just because they don't play, this shouldn't interfere with their streaks. Therefore if a player wins two weeks running, then misses a week, and then wins the next two, his winning streak is still 4 over those five weeks. The streaks should only be restarted after a change in W or L.

I've set out an example below. This shows three players records over a 15 week period (my actual spreadsheet is for 36 weeks), although currently we have only played up to Week 11. Obviously W and L indicate a Win or Loss that week, whereas X indicates they did not play that week. All of these W's, L's and X's are automatically inserted into these cells by a formula dependent on data from elsewhere in my workbook. I am looking for formulas to go into columns Q, R & S, and I have indicated what the results should be.

Note that the array should always be B:P, i.e. all weeks up to the end of the season irrespective of whether the games have been played or not. Results are updated weekly and therefore the streaks need to automatically change as the data is populated into the new weeks.

Please also note that I am looking for formulas only. I can't do VBA!!

I hope this is clear, and I appreciate any help you can give.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

1​

WEEK 1​

WEEK 2​

WEEK 3​

WEEK 4​

WEEK 5​

WEEK 6​

WEEK 7​

WEEK 8​

WEEK 9​

WEEK 10​

WEEK 11​

WEEK 12

WEEK 13

WEEK 14

WEEK 15

LONGEST W STREAK​

LONGEST L STREAK​

CURRENT STREAK​

2​

John

W​

W​

X​

W​

W​

L​

X​

L​

W​

W​

W​

4​

2​

W3​

3​

Peter

X​

W​

L​

L​

W​

X​

X​

W​

X​

W​

W​

4​

2​

W4​

4​

George

X​

L​

L​

X​

L​

W​

X​

L​

X​

L​

X​

1​

3​

L2​

 

[Scott T]

Are all your letters the same with no extra spaces?

If you have "W" in one cell and "W " (W with a space after it) the formula will not see them as the same and will not count it as a streak.

 

[Telman86]

OK, I've just cleared all the data from my cells B2:P5 and re-entered them. Now it works perfectly! That must have been the problem as I copied and pasted the table from Bo_Ry's post earlier rather than enter the data.

Thank you so much guys for all your help!

 

[lrobbo314]

No VBA, but how about a Power Query solution.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Name	WEEK 1	WEEK 2	WEEK 3	WEEK 4	WEEK 5	WEEK 6	WEEK 7	WEEK 8	WEEK 9	WEEK 10	WEEK 11	WEEK 12	WEEK 13	WEEK 14	WEEK 15	Longest W Streak	Longest L Streak	Custom
2	John	W	W	X	W	W	L	X	L	W	W	W	L				4	2	L1
3	Peter	X	W	L	L	W	X	X	W	X	W	W	W				5	2	W5
4	George	X	L	L	X	L	W	X	L	X	L	X	L				1	3	L3
Sheet1

Code for Longest Streak Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Reorder = Table.ReorderColumns(Index0,{"Name", "Index", "WEEK 1", "WEEK 2", "WEEK 3", "WEEK 4", "WEEK 5", "WEEK 6", "WEEK 7", "WEEK 8", "WEEK 9", "WEEK 10", "WEEK 11", "WEEK 12", "WEEK 13", "WEEK 14", "WEEK 15"}),
    Remove0 = Table.RemoveColumns(Reorder,{"Name"}),
    Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
    Index1 = Table.AddIndexColumn(Unpivot, "Index.1", 0, 1),
    FilterX = Table.SelectRows(Index1, each ([Value] <> "X")),
    Group0 = Table.Group(FilterX, {"Index", "Value"}, {{"Count",
        each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "rowindexinsubset" ,1,1 ),
        type table}},GroupKind.Local),
    GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"rowindexinsubset"))),
    Group1 = Table.Group(GetMax, {"Index", "Value"}, {{"Count", each List.Max([Custom]), type number}}),
    Pivot = Table.Pivot(Group1, List.Distinct(Group1[Value]), "Value", "Count"),
    Rename = Table.RenameColumns(Pivot,{{"W", "Longest W Streak"}, {"L", "Longest L Streak"}}),
    Remove1 = Table.RemoveColumns(Rename,{"Index"})
in
    Remove1

Code for Latest Streak Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Reorder = Table.ReorderColumns(Index0,{"Name", "Index", "WEEK 1", "WEEK 2", "WEEK 3", "WEEK 4", "WEEK 5", "WEEK 6", "WEEK 7", "WEEK 8", "WEEK 9", "WEEK 10", "WEEK 11", "WEEK 12", "WEEK 13", "WEEK 14", "WEEK 15"}),
    Remove0 = Table.RemoveColumns(Reorder,{"Name"}),
    Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
    Index1 = Table.AddIndexColumn(Unpivot, "Index.1", 0, 1),
    FilterX = Table.SelectRows(Index1, each ([Value] <> "X")),
    Group0 = Table.Group(FilterX, {"Index", "Value"}, {{"Count",
        each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "rowindexinsubset" ,1,1 ),
        type table}},GroupKind.Local),
    GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"rowindexinsubset"))),
    Index2 = Table.AddIndexColumn(GetMax, "Index.1", 0, 1),
    Group1 = Table.Group(Index2, {"Index"}, {{"Count", each _, type table}}),
    GetLast = Table.AddColumn(Group1, "Custom", each List.Last(Table.Column([Count],"Value")) & Text.From(List.Last(Table.Column([Count],"Custom")))),
    Remove1 = Table.RemoveColumns(GetLast,{"Index", "Count"}),
    Rename = Table.RenameColumns(Remove1,{{"Custom", "Current Streak"}})
in
    Rename

 

[lrobbo314]

Was able to consolidate the M code to just 1 table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Remove0 = Table.RemoveColumns(Index0,{"Name"}),
    Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
    FilterX = Table.SelectRows(Unpivot, each ([Value] = "L" or [Value] = "W")),
    Index1 = Table.AddIndexColumn(FilterX, "Index.1", 0, 1),
    Group0 = Table.Group(Index1, {"Index", "Value"}, {{"Count",
        each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "subsetIndex" ,1,1 ),
        type table}},GroupKind.Local),
    GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"subsetIndex"))),
    Group1 = Table.Group(GetMax, {"Index"}, {{"Count", each _, type table}}),
    wStreak = Table.AddColumn(Group1, "Longest W Streak", each List.Max(Table.SelectRows([Count], each [Value]="W")[Custom])),
    lStreak = Table.AddColumn(wStreak, "Longest L Streak", each List.Max(Table.SelectRows([Count], each [Value]="L")[Custom])),
    LastStreak = Table.CombineColumns(Table.TransformColumnTypes(Table.ExpandRecordColumn(Table.AddColumn(lStreak, "Custom.1", each Table.Last([Count])), "Custom.1", {"Value", "Custom"}, {"Value", "Custom"}), {{"Custom", type text}}, "en-US"),{"Value", "Custom"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Last Streak"),
    Remove1 = Table.RemoveColumns(LastStreak,{"Index", "Count"})
in
    Remove1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Name	WEEK 1	WEEK 2	WEEK 3	WEEK 4	WEEK 5	WEEK 6	WEEK 7	WEEK 8	WEEK 9	WEEK 10	WEEK 11	WEEK 12	WEEK 13	WEEK 14	WEEK 15	Longest W Streak	Longest L Streak	Last Streak
2	Alan	W	W	W	W	X	W	L	L	L	L	W	W				5	4	W2
3	Barb	L	X	X	L	W	L	W	X	L	L	L	W	L			1	3	L1
4	Cain	X	L	L	W	X	W	W	L	X	L	L	W	L			3	3	L1
5	Dana	W	L	L	L	L	W	L	X	L	W	W	W				3	4	W3
6	Eric	W	L	W	L	X	W	X	L	W	L	L	W	W	W		3	2	W3
7	Fran	X	L	L	W	L	W	X	L	W	L	W					1	2	W1
8	Gary	L	L	L	L	L	X	X	X	W	W	X	L	L	L	L	2	5	L4
9	Hana	L	L	X	X	W	X	W	X	X	W	X	W	W	X	W	5	2	W5
10	Ivan	W	W	L	W	L	L	W	W	L	X	W	L				2	2	L1
11	Jane	L	W	L	W	X	W	W	X	L	L	X	W	W	X		3	2	W2
12	Kent	W	L	L	L	W	L	W	W	L	X	X	L	X			2	3	L2
13	Leah	X	W	W	W	X	X	X	L	W	W	X	W				3	1	W3
14	Mark	W	W	W	W	W	L	L	W	W	X	L	L	X	W		5	2	W1
15	Nina	L	L	L	L	X	L	L	W	X	L	W	L	L	X	L	1	6	L3
16	Rob	L	W	L	L	W	W	W	W	X	W	W	X	W			7	2	W7
Sheet1

 

[djog]

Hello, hope it's fine if I just reuse this topic. Very similar question to OPs, answers here have been excellent but I lost almost the entire afternoon today trying to dissect given formulas to get what I want so I figured it's best to just create an account and ask!

So I'm looking to get formulas for current winning, unbeaten, winless and losing streak; any ideas? Thanks a lot!

 

[Eric W]

Welcome to the MrExcel forum!

It's ok to add to an existing thread, in that there's no rule against it, but you run the risk that no one will notice your post. It's usually best to open a new thread with a question. Also, check out the XL2BB tool. There's a link in my signature, and in the reply box. It makes it so people can copy your sample sheet directly from the post, and then they won't have to retype everything which is the case if you only post a picture. You're more likely to get more and faster help if you do. It's what I use below.

Given that, consider this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1
2		TOTAL	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	W	WD	DL	L	W	WD	DL	L
3	1	Team1	W	D	W	D	L	D	W	D	L	W	W	W	W	W	L	W	W	W	D																						0	4	1	0
4	7	Team2	L	D	L	D	W	W	L	D	L	D	W	W	L	L	L	D	W	W	L																						0	0	1	1
5	6	Team3
6	2	Team4
7	5	Team5
8	9	Team6
9	3	Team7
10	4	Team8
11	8	Team9
12	10	Team10
13																																							Longest				Current
Sheet4

Cell Formulas
Range	Formula
AQ3:AQ4	AQ3	=COUNTIF(INDEX(3:3,AGGREGATE(14,6,COLUMN($C3:$AL3)/(($C3:$AL3="D")+($C3:$AL3="L")+($C$2:$AL$2=1)),1)):$AL3,"W")
AR3:AR4	AR3	=SUM(COUNTIF(INDEX(3:3,AGGREGATE(14,6,COLUMN($C3:$AL3)/(($C3:$AL3="L")+($C$2:$AL$2=1)),1)):$AL3,{"W","D"}))
AS3:AS4	AS3	=SUM(COUNTIF(INDEX(3:3,AGGREGATE(14,6,COLUMN($C3:$AL3)/(($C3:$AL3="W")+($C$2:$AL$2=1)),1)):$AL3,{"L","D"}))
AT3:AT4	AT3	=COUNTIF(INDEX(3:3,AGGREGATE(14,6,COLUMN($C3:$AL3)/(($C3:$AL3="D")+($C3:$AL3="W")+($C$2:$AL$2=1)),1)):$AL3,"L")

This should do what you want. I only typed in a couple of test rows, so make sure it works on the rest of your data.

Also, I wasn't sure what to do with the blank spaces in column R.

Good luck!

 

[djog]

Thanks Eric, it works like a charm!

Yeah that's a great tool, goot to know for next time.

The two blank spaces are from a postponed game which is yet to be played

 

[Eric W]

Glad it works for you! Thanks for the feedback.

 

[lrobbo314]

Doing the whole thing, longest and current, using Power Query.

Streaky.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
2	Total	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	W (Long)	WD (Long)	LD (Long)	L (Long)	W (Current)	WD (Current)	WL (Current)	L (Current)
3	Team 1	D	L	W	D	W	W	W	D	W	L	W	D	D	L	D	L	L	L	W																		3	7	7	3	1	1	0	0
4	Team 2	D	D	L	D	W	W	D	W	W	W	L	D	D	D	D	L	D	D	W																		3	7	8	1	1	3	0	0
5	Team 3	L	W	L	D	L	L	L	L	W	D	W	W	L	W	W	L	L	D	W																		2	4	6	4	1	2	0	0
6	Team 4	D	D	L	D	D	D	L	L	W	D	D	L	L	L	W	W	W	W	D																		4	5	8	3	0	5	1	0
7	Team 5	D	D	D	L	D	L	W	W	D	L	D	W	W	D	D	D	L	L	W																		2	6	6	2	1	1	0	0
8	Team 6	W	D	D	W	D	L	W	L	D	L	W	W	W	L	L	L	W	D	L																		3	5	3	3	0	0	2	1
9	Team 7	W	D	W	D	D	L	D	L	D	W	W	D	L	W	D	D	L	D	W																		2	5	6	1	1	2	0	0
10	Team 8	L	D	W	D	L	D	W	D	W	W	W	D	W	L	W	D	L	L	W																		3	8	3	2	1	1	0	0
11	Team 9	L	D	L	L	L	D	W	W	L	W	L	W	W	W	W	W	L	W	W																		5	5	6	3	2	2	0	0
12	Team 10	D	D	L	W	D	D	L	D	L	D	W	D	L	D	L	L	W	L	L																		1	3	6	2	0	0	2	2
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Total"}, "Attribute", "Value"),
    Types = Table.TransformColumnTypes(Unpivot,{{"Total", type text}, {"Value", type text}, {"Attribute", Int64.Type}}),
    DtoW = Table.DuplicateColumn(Types, "Value", "WD"),
    DtoL = Table.DuplicateColumn(DtoW, "Value", "LD"),
    ReplaceW = Table.ReplaceValue(DtoL,"D","W",Replacer.ReplaceText,{"WD"}),
    ReplaceL = Table.ReplaceValue(ReplaceW,"D","L",Replacer.ReplaceText,{"LD"}),
    GroupTeam = Table.Group(ReplaceL, {"Total"}, {{"Count", each _, type table}}),
    WL = Table.TransformColumns(GroupTeam, {{"Count", (tbl)=> Table.Group(tbl,"Value",{"Total", each Table.RowCount(_)},GroupKind.Local)}}),
    GroupWL = Table.TransformColumns(WL, {{"Count", (tbl)=> Table.Group(tbl,"Value",{"Max", each List.Max(_[Total])})}}),
    WD = Table.TransformColumns(GroupTeam, {{"Count", (tbl)=> Table.Group(tbl,"WD",{"Total", each Table.RowCount(_)},GroupKind.Local)}}),
    GroupWD = Table.TransformColumns(WD, {{"Count", (tbl)=> Table.Group(tbl,"WD",{"Max", each List.Max(_[Total])})}}),
    LD = Table.TransformColumns(GroupTeam, {{"Count", (tbl)=> Table.Group(tbl,"LD",{"Total", each Table.RowCount(_)},GroupKind.Local)}}),
    GroupLD = Table.TransformColumns(LD, {{"Count", (tbl)=> Table.Group(tbl,"LD",{"Max", each List.Max(_[Total])})}}),
    Index = Table.AddIndexColumn(GroupLD, "Index", 0, 1),
    WinsLong = Table.AddColumn(Index, "W (Long)", each Table.SelectRows(GroupWL[Count]{[Index]}, each _[Value] = "W"){0}[Max]),
    LossLong = Table.AddColumn(WinsLong, "L (Long)", each Table.SelectRows(GroupWL[Count]{[Index]}, each _[Value] = "L"){0}[Max]),
    uLong = Table.AddColumn(LossLong, "WD (Long)", each Table.SelectRows(GroupWD[Count]{[Index]}, each _[WD] = "W"){0}[Max]),
    bLong = Table.AddColumn(uLong, "LD (Long)", each Table.SelectRows(GroupLD[Count]{[Index]}, each _[LD] = "L"){0}[Max]),
    wCurrent = Table.AddColumn(bLong, "W (Current)", (tbl)=> Table.TransformColumns(WL,{{"Count", each Table.SelectRows(Table.LastN(_,1), (t)=> t[Value]="W")}}){tbl[Index]}[Count][Total]{0}),
    lCurrent = Table.AddColumn(wCurrent, "L (Current)", (tbl)=> Table.TransformColumns(WL,{{"Count", each Table.SelectRows(Table.LastN(_,1), (t)=> t[Value]="L")}}){tbl[Index]}[Count][Total]{0}),
    wdCurrent = Table.AddColumn(lCurrent, "WD (Current)", (tbl)=> Table.TransformColumns(WD,{{"Count", each Table.SelectRows(Table.LastN(_,1), (t)=> t[WD]="W")}}){tbl[Index]}[Count][Total]{0}),
    wlCurrent = Table.AddColumn(wdCurrent, "WL (Current)", (tbl)=> Table.TransformColumns(LD,{{"Count", each Table.SelectRows(Table.LastN(_,1), (t)=> t[LD]="L")}}){tbl[Index]}[Count][Total]{0}),
    REW = Table.ReplaceErrorValues(wlCurrent, {{"W (Current)", 0}}),
    REL = Table.ReplaceErrorValues(REW, {{"L (Current)", 0}}),
    REWD = Table.ReplaceErrorValues(REL, {{"WD (Current)", 0}}),
    REWL = Table.ReplaceErrorValues(REWD, {{"WL (Current)", 0}}),
    Reorder = Table.ReorderColumns(REWL,{"Total", "Count", "Index", "W (Long)", "WD (Long)", "LD (Long)", "L (Long)", "W (Current)", "WD (Current)", "WL (Current)", "L (Current)"}),
    Select = Table.SelectColumns(Reorder,{"W (Long)", "WD (Long)", "LD (Long)", "L (Long)", "W (Current)", "WD (Current)", "WL (Current)", "L (Current)"})
in
    Select