calculating win/loss streaks macro

kraps2312

New Member
Joined
Jun 27, 2009
Messages
30
Looking for a macro that will take a column of W and Ls (as in the code or pic below) and count the length of each streak and place in the win/loss columns below last streak that was entered.

1st streak = 2 and is for L so C2 will have a 2 entered.
2nd streak = 1 and is for W so B2 will have a 1 entered.
3rd streak = 2 and is for L so C3 will have a 2 entered since C2 already has a value in it.
4th streak = 1 and is for W so B3 will have a 1 entered since B2 already has a value in it.
5th streak = 1 and is for L so C4 will have a 1 entered since C3 already has a value in it.
6th streak = 4 and is for W so B4 will have a 4 entered since B3 already has a value in it... and so on.

Thank you.
Note: Column A can contain data down to A100000
(Time for me to learn how to write a macro by seeing one that works)
Code:
result	win	lose
L	1	2
L	1	2
W	4	1
L	1	3
L	7	2
W	2	1
L	1	1
W		
W		
W		
W		
L		
L		
L		
W		
L		
L		
W		
W		
W		
W		
W		
W		
W		
L		
W		
W		
L		
W
(Highlighted L in yellow is just for show)
streak.jpg
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try:
Code:
Sub BinaryDanceForMe ()
Dim i As Long, w As Long, l As Long

w = 1
l = 1

Application.ScreenUpdating = False

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i + 1) = Range("A" & i) Then
        If Range("A" & i) = "L" Then
            l = l + 1
        Else
            w = w + 1
        End If
    Else
        If ("A" & i) = "L" Then
            Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = l
            l = 1
        Else
            Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = w
            w = 1
        End If
    End If
Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thank you for the fast reply. It is close.
The pic below shows the results of your code.

I will have time later to look thru the code, It looks easy to figure out.

See if I can get it to fill in both win and loss columns B4 you do :)
streak2.jpg

Try:
Code:
Sub BinaryDanceForMe ()
Dim i As Long, w As Long, l As Long

w = 1
l = 1

Application.ScreenUpdating = False

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i + 1) = Range("A" & i) Then
        If Range("A" & i) = "L" Then
            l = l + 1
        Else
            w = w + 1
        End If
    Else
        If ("A" & i) = "L" Then
            Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = l
            l = 1
        Else
            Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = w
            w = 1
        End If
    End If
Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
This is what I got after I ran my code which populates both win and lose columns as required - not sure why you didn't:

Result Win Lose
L 4 2
L 1 1
W 3 1
W 4 2
W 1 1
W 3 1
L 4 2
W 1 1
L 3 1
W 4 2
W 1 1
W 3 1
L
L
W
W
W
W
L
W
L
W
W
W
L
L
W
W
W
W
L
W
L
W
W
W
L
L
W
W
W
W
L
W
L
W
W
W
 
Last edited:
Upvote 0
I use windows Xp with Excel 2007 and it did not work both in .xls or .xlsx

I will look at it more tonight and post update
 
Upvote 0
This is what I got after I ran my code which populates both win and lose columns as required - not sure why you didn't:

Result Win Lose
L 4 2
L 1 1
W 3 1
W 4 2
W 1 1
W 3 1
L 4 2
W 1 1
L 3 1
W 4 2
W 1 1
W 3 1
L
L
W
W
W
W
L
W
L
W
W
W
L
L
W
W
W
W
L
W
L
W
W
W
L
L
W
W
W
W
L
W
L
W
W
W
Tested code on 3 laptops. Excel 2000,2003 and 2007 and they all do not work for me or my friends.
If the data column is small...wwwlllwwll as oppossed to WWWLLL capital is does place the correct streaks in column B as can be seen in the code box below.
The capital WL returns what I do not know???

But...using
=OFFSET(B$2,((ROW()-ROW(F$2))*2),0,1,1) will take every 2nd cell for w streak
=OFFSET(B$3,((ROW()-ROW(G$2))*2),0,1,1) will take every 2nd cell for L streak
I can now have my 2 columns of w/l streaks :)

I do not know why the code does not work for me or my friends excel. I tried changing a few things but always the same results!
At least I can use it!
If I find the solution I will post the results :)
Code:
r	w	L	r	w	L
W	3		w	3	
W	1		w	3	
W	2		w	2	
L	1		l	2	
L	1		l	1	
L	1		l	1	
W	6		w	6	
W	1		w	5	
L	4		l	4	
L	1		l	3	
W			w		
L			l		
W			w		
W			w		
W			w		
W			w		
W			w		
W			w		
L			l		
L			l		
L			l		
L			l		
L			l		
W			w		
W			w		
W			w		
W			w		
L			l		
L			l		
L			l
 
Upvote 0
Calculating win/loss streaks macro

That's really odd, the red parts determine what column the values go into:
Rich (BB code):
        If ("A" & i) = "L" Then
            Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = l
            l = 1
        Else
            Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = w
            w = 1
        End If
 
Upvote 0
Just tested this on Excel 2003 (at work) and made a few mods but REALLY should work now (yes a small mistake in my previous code)!
Code:
Sub BinaryDanceForMe()
Dim i As Long, w As Long, l As Long
w = 1
l = 1
Application.ScreenUpdating = False
For i = 2 To Range("A" & Rows.Count).End(xlUp).row
    If UCase(Range("A" & i + 1)) = UCase(Range("A" & i)) Then
        If UCase(Range("A" & i)) = "L" Then
            l = l + 1
        Else
            w = w + 1
        End If
    Else
        If UCase(Range("A" & i)) = "L" Then
            Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = l
            l = 1
        Else
            Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = w
            w = 1
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Win</TD><TD style="FONT-WEIGHT: bold">Lose</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>W</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>W</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>W</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">42</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">43</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">44</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">45</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">46</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">47</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">48</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">49</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">50</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">51</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">52</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">53</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">54</TD><TD>W</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">55</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">56</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">57</TD><TD style="BACKGROUND-COLOR: #00ff00">L</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">58</TD><TD>W</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top