Selected Answer
Thomas
This can be done using VBA as follows (other approaches are possible!)
In the attached file, I've put your sequence in column A of worksheet Data (and duplicated some values). The code in Module 1 (below) grabs whatever is in column A (it coulld be very many rows), strips out D values putting the remaning W/L sequence into an array (for speed). It then loops through the array creating 4-game sequences (like WWLW) and creates a dictionary where each repeated sequence is counts (under the Item value). That gets copied to the second sheet. I've added comments to help you see what's going on...
Option Base 1
Sub WLnoDcount()
Dim SortArray As Variant, SortArrayTemp As Variant
Dim m As Integer, n As Integer
Dim wsIn As Worksheet, wsOut As Worksheet
Dim Games As String
Dim WLdict As Object
Set wsIn = ThisWorkbook.Sheets("Data")
Set wsOut = ThisWorkbook.Sheets("Sequence totals")
Set WLdict = CreateObject("Scripting.Dictionary")
' copy data into array
With wsIn
SortArrayTemp = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With
'set initial size of array
ReDim SortArray(UBound(SortArrayTemp, 1))
'Loop through Range and add values other than D to the array i.e. remove D values
m = 0
For n = LBound(SortArrayTemp, 1) To UBound(SortArrayTemp, 1)
If SortArrayTemp(n, 1) <> "D" Then
m = m + 1
SortArray(m) = SortArrayTemp(n, 1)
End If
Next n
' Correct the size of the array (now free of D values), preserving contents
ReDim Preserve SortArray(m)
'create 4 game sequences and increase count in a dictionary each time a combination is found
For n = LBound(SortArray) To UBound(SortArray) - 3
Games = SortArray(n) & SortArray(n + 1) & SortArray(n + 2) & SortArray(n + 3)
If Not WLdict.exists(Games) Then WLdict.Add Key:=Games, Item:=0
WLdict(Games) = WLdict(Games) + 1
Next n
' copy dictionary contents to columns A & B of wsOut (clearing first)
m = WLdict.Count
With wsOut
.Range("A:B").ClearContents
.Range("A1").Value = "W/L sequence"
.Range("B1").Value = "Occurrences"
.Range("A2:A" & m + 1).Value = WorksheetFunction.Transpose(WLdict.Keys)
.Range("B2:B" & m + 1).Value = WorksheetFunction.Transpose(WLdict.Items)
End With
'tell the user something happened
MsgBox m & " sequence totals added to sheet " & wsOut.Name
End Sub
I leave you to check the totals work and to sort the totals the code produces (by occurrences or sequence) then run on your full data set. You can change bold bits in the lines
Set wsIn = ThisWorkbook.Sheets("Data")
Set wsOut = ThisWorkbook.Sheets("Sequence totals")
to suit whatever you call your sheets
I've done this in the second file (attached below, named ...v0_b.xlsm) where your 55 rows of test data are used (and the game data collected from column H not A). There's a green shape on the first sheet called "Run macro" - just click that and the revised code will run and present the results wth (near) German titles on the other sheet. Once you see that is working okay with test data, paste your 40,000 rows in sheet Tabell1 and the macro will handle that (provided the W, L and D values are still in column H).
As I said, for me these are unrelated results, spaced in time and the number of occurrences has no direct result on future wins or losses (so don't blame me if you bet money on outcomes based on the results!).
