Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Data analysis of 3 consecutive rows

0

Hello,

it would be really great, if someone can answer me of how I solve this scenario. I do have a sportsdata file with certain data cells and about 40,000 rows. Each row ends with a data cell that shows L, W or D(to be ignored)

the data can look like this sample:

1 L

2 W

3 L

4 L

5 L

6 W

7 W

8 W

9 L

10 W

11 L

12 W

13 W

and so on....

The goal is to analyse the sequence of 3 rows. According to above sample, cell 1, 2 and 3 shows LWL, cell 2,3 and 4 shows WLL - cell 3,4,5 shows LLL and so on until the final sequence of row 11,12,13 which is LWW. The data I am interested in is, what is the next data after each sequence of 3, to be put into a next data cell.

The above sample for rows 1,2,3 has the same sequense as rows 9,10,11, which is LWL. The very next cell shows the data in row4 = L and in row12, it is W.

My analysing cell should look like this:

sequence - W L

LWL.        1   1

WLL              1

LLL          1

LLW         1

LWW       1

WWW            1

WWL       1

WLW       2

Important is that any "D" value is to be ignored. There are about 10% "D" values in my database.

This new table tells me that I had 1 time W and 1 time L sofar, if the sequence LWL appeared. Or the sequence WLW had 2 times W in the followed next 4th cell.

Now, how can I settle my problem? I am using Excel365 family version. Can this be solved using any formula or VBA code?

I am looking forward to your good solution

Thank you very much

Thomas

Answer
Discuss

Discussion

Hi Thomas,
Please edit your question to add more information and - most importantly - atttach a workbook containing a sample of the expected result.
As it is, your question doesn't mention how the Ws and Ls come about. Are they random? Apparently you want to predict the next letter. If so, by which pattern can it be predicted? Your sample data don't include any Ds. If they are to be ignored then any effort at predicting the next letter could be tuned not to suggest any Ds.
You also appear to want to count existing Ws and Ls but not sequences. That looks like an easy task made difficult by the absence of a table where you want the results. Your question doesn't state how the count affects the search for sequences or how it helps determine the next letter or sequence of letters or how a D could be included in such a sequence.
Variatus (rep: 4879) Jun 30, '21 at 9:36 pm
Thomas

Do L, D and W signify Loss, Draw and Win in your sport? (In which case you'll know that analysis of past patterns/ their frequency isn't a reliable predictor of future sequences!) If so, it seems unlikely that your "40,000 rows" will relate to one team/side/player so how are they differentiated please? (If this is a paramter, kindly edit your original question, so all the information is in one place)

When a D occurs in the results (say the 15th row) can that cell be skipped and likewise if 2or more D's interfere with a sequence of Ls and Ws? E.g. if results 13 on went W L D D W L; a first sequence of WLW be recorded, followed by a 4th result L, right?

Note too that it often helps if you attach an Excel file for us to see (use the Add Files... button when you edit your original question).
John_Ru (rep: 3452) Jul 1, '21 at 3:02 am
Hello John_Ru. You are right, I should add a sample file. The one I am adding is showing one out of 55 testaccounts. Each one day is one tip which ends in a W(win), L(loss) or D(draw). 
This is not a prediction idea, rather see how many times the 4th cell is a W or a L.
D cells can be skipped as it is counted as if that match has not been played, like match was canceled or postponed or the score is putting the match into a D result.
Thank you very much that you took up the matter
Thomas
daytrader (rep: 2) Jul 1, '21 at 10:41 am
Hello Variatus, thank you very much for your taking up this matter.
This is not for predicting the next outcome. I am well aware that this is not going to work. I have added meanwhile a sample file for you to look at it again. Your questions may have been answered.
If it is possible to solve the issue using a formula or function would be great. I am not very familliar with VBA. I may have to learn how to use VBA.
Thank you very much
Thomas
daytrader (rep: 2) Jul 1, '21 at 10:49 am
Add to Discussion

Answers

0
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!).

Discuss

Discussion

Hello John_Ru... I see that you posted an answer. Great.... Meanwhile I also send a sample file, which represents looking like the other 54 testaccounts. Does this help?
It is a bid difficult for me to use a VBA code, as I did not do it before. So, I have to learn how to use a coded solution. Should not be too difficult I hope... :-) ?
Thank you very much again for your support.
Thomas
daytrader (rep: 2) Jul 1, '21 at 10:53 am
Thomas, please see revised Answer (and second file).
John_Ru (rep: 3452) Jul 1, '21 at 11:20 am
Hello John... it worked... thank you very much.
What do I need the file ...v0 a.xlms for?
Your macro works well for my pattern3 criteria. What do I need to change, If I wish to increase the pattern to 4, 5 and 6?
Would that be a big issue to add/change the macro?
Thank you very much again,
Thomas
daytrader (rep: 2) Jul 4, '21 at 10:21 am
Thomas.

I'm glad it worked (and thanks for selecting my Answer).

You don't need the ...v0 a.xlms file but I left it there in case other users need it in future (and might be confused by the German words in v0 b.xlms).

Normally we don't give extended solutions in these discussions under an answer/ question title (though another question might be raised). I will this time but I won't put this in my Answer (since your question refers to only 3 data rows)...

It's easy to change the macro- just two lines need to altered (but your results will have more combinations with smaller numbers each). To get longer sequences, you have to increase the number in the For... line (so near the end, the loop stops before there are too few matches for your sequence) and add other elements of the array (to look ahead to in the sequence). For a sequence of 5 numbers, the changes are in bold below (scroll right to see the extra array element added to the "word"):
For n = LBound(SortArray) To UBound(SortArray) - 4
 Games = SortArray(n) & SortArray(n + 1) & SortArray(n + 2) & SortArray(n + 3) & SortArray(n + 4)
    If Not WLdict.exists(Games) Then WLdict.Add Key:=Games, Item:=0
    WLdict(Games) = WLdict(Games) + 1
Next n
If you wanted a 6-letter, sequence, the number would be 5 and you'd add & SortArray(n + 5) to the end of the Games... line above (etc.).
John_Ru (rep: 3452) Jul 4, '21 at 12:32 pm
If you don't know how to make the changes to the code, I suggest you look at Don's tutorial Excel Macro VBA Tip 1 - How to Write Your Very First Macro in Microsoft Excel
John_Ru (rep: 3452) Jul 4, '21 at 12:32 pm
Hi John, thank you very much... it worked as you well explained. Me, as a total Newbie, did understand. Great job.... thanks again
Thomas
daytrader (rep: 2) Jul 5, '21 at 11:20 am
Add to Discussion
0

If your intention is to know how many times the 4th value is a Loss (L) you may simply enter =H5="L" in cell I2. It will return True or False. As you copy it down the referenced H5 will change to H6, H7, H8 etc - always examining the cell 4 rows below the one in which the formula reesides. On the last 3 rows it will return a wrong result because the referenced cells are blank. Therefore the formula below prevents any result from being calculated in that case.

=IF(LEN(H5),H5="L","")

Of course, you can create variations of the above, like H5="W", H5="D", H5=H2 or H5<>H2. And you can use the True or False result instead of displaying it. For example, =IF(LEN(H5),IF(H5=H2,"is same","is different"),"").

After copying the formula to all cells in column I you can use the next formula to count the results. It will tell you how many times the 4th value was an "L"

=COUNTIF(I:I,TRUE)

=COUNTIF(I:I,FALSE) will indirectly count how many times the value was not an "L". It's indirect because the formula counts the number of False values and will not include blank cells in the count (on the argument that blank cells are "not L"). 

Discuss

Discussion

Hi Variatus, I understand, but the solution does not take the pattern into account. A pattern must be identified and then the 4th cell is the countable value.
So, in the example file, the first pattern of 3 values is LLW... now, how many times this pattern occured over all times and with many values for  "W or L" in its 4th cell. So, the interesting data is that count in the 4th cell for that particular pattern. I did that manually and the pattern LLW occured 60 times with 35W and 25L in summary of the followed 4th cell.... But as said I did that manually.... Overall there will be 8 sets of pattern3 available of which I will need my data for...Any chance to get a function/calculation for it? 
Thank you so much you took up this matter.
Thomas
daytrader (rep: 2) Jul 4, '21 at 10:34 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login