Help with a STREAK formula

c0087

New Member
Joined
Jul 13, 2015
Messages
43
- I have a streak in column B
- I need a macro/formula to only return the end of each streak (no zeros) in column D
- The result should look like column D

Thanks in advance


ABCDEFGHIJKLMNOPQRST
1
1
04
12
23
3
4
0
1
2
0
0
0
1
2
3

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
Here is a macro that should work for you...
Code:
[table="width: 500"]
[tr]
	[td]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Replace(Join(Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp))), ""), 0, " ")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/td]
[/tr]
[/table]
 

c0087

New Member
Joined
Jul 13, 2015
Messages
43
Here is a macro that should work for you...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Replace(Join(Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp))), ""), 0, " ")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the quick reply
It almost works. Below is a copy from my actual spreadsheet
As you can see, it should put out 12,1 rather than 10,4

1
WL1
103
0110
104
011
026
032
105
204
306
0110
1012
204
304
407
502
607
701
801
901
1002
1103
1201
014
101
012
10
20
30
40
50
60
01
10
20
01
10
20
30
40
50
01
02
10
20
30
40
01
10
20
30
40
50
60
01
10
20
30
40
50
60
70
80
90
100
110
120
130
140
150
160
01
10
20
30
40
01
10
20
30
40
01
10
20
30
40
50
60
70
01
02
10
20
01
10
20
30
40
50
60
70
01
10
01
02
03
10
01
10
01
10
20
01
10
20
30
01
02
03
04
10
01
02
03
10
20
30
40
01
10
01
10
20
00
00

<colgroup><col width="67" span="4" style="width:50pt"> </colgroup><tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...
Code:
[table="width: 500"]
[tr]
	[td]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(B2:B" & Cells(Rows.Count, "B").End(xlUp).Row & "=0,"" "",""X"")")), "")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/td]
[/tr]
[/table]
 

c0087

New Member
Joined
Jul 13, 2015
Messages
43
Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(B2:B" & Cells(Rows.Count, "B").End(xlUp).Row & "=0,"" "",""X"")")), "")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank you sir - works perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top