Help with Current and highest streaks

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,594
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
No VBA, but how about a Power Query solution.

Book1
ABCDEFGHIJKLMNOPQRS
1NameWEEK 1WEEK 2WEEK 3WEEK 4WEEK 5WEEK 6WEEK 7WEEK 8WEEK 9WEEK 10WEEK 11WEEK 12WEEK 13WEEK 14WEEK 15Longest W StreakLongest L StreakCustom
2JohnWWXWWLXLWWWL42L1
3PeterXWLLWXXWXWWW52W5
4GeorgeXLLXLWXLXLXL13L3
Sheet1


Code for Longest Streak Table
Code:
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
Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
Was able to consolidate the M code to just 1 table.

Code:
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
ABCDEFGHIJKLMNOPQRS
1NameWEEK 1WEEK 2WEEK 3WEEK 4WEEK 5WEEK 6WEEK 7WEEK 8WEEK 9WEEK 10WEEK 11WEEK 12WEEK 13WEEK 14WEEK 15Longest W StreakLongest L StreakLast Streak
2AlanWWWWXWLLLLWW54W2
3BarbLXXLWLWXLLLWL13L1
4CainXLLWXWWLXLLWL33L1
5DanaWLLLLWLXLWWW34W3
6EricWLWLXWXLWLLWWW32W3
7FranXLLWLWXLWLW12W1
8GaryLLLLLXXXWWXLLLL25L4
9HanaLLXXWXWXXWXWWXW52W5
10IvanWWLWLLWWLXWL22L1
11JaneLWLWXWWXLLXWWX32W2
12KentWLLLWLWWLXXLX23L2
13LeahXWWWXXXLWWXW31W3
14MarkWWWWWLLWWXLLXW52W1
15NinaLLLLXLLWXLWLLXL16L3
16RobLWLLWWWWXWWXW72W7
Sheet1
 

Forum statistics

Threads
1,082,607
Messages
5,366,593
Members
400,905
Latest member
xcelstudent

Some videos you may like

This Week's Hot Topics

Top