Count between Hi and Low

[smandula]

How do I count the rows between Hi and Low stock values?
And Low to Hi.
Col B C
58.0 Low
59.7
63.0 Hi
58.5
60.4
62.2 Low
65.4
63.0
64.3
66.4 Hi
70.0 Hi
66.4
64.0
62.0 Low
Put result in Col D



With Thanks

 

[Guest]

One way that I sole this general problem is in the next available column to
the right I create an index with triggers.. i.e. the following, D2 reffering
the cell in column D next to say, "Low":

D2 = if(C2="Low",1,if(C2="High",-1,0))
D3 = if(C3="Low",1,if(C2="High",-1,0))

E2 = D2
E3 = E2 + D3

F2 = if(E2=1,1,0)
G2 = if(E2=-1,1,0)

Then sum up either F2:F20 or G2:G20 at the end to get the number of rows
between the low to high or high to low.

You can also add triggers so if you only want the first interval or second
interval etc.

Jonathan

 

[Charles Harmon]

Hi,
You can try this:

Sub HiLowCnt()
Dim cell As Range, rng As Range
Dim prev
Dim hicnt As Integer, locnt As Integer
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng

If cell.Offset(0, 1) = "Low" Then
locnt = locnt + 1
prev = "Low"
ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then
locnt = locnt + 1
End If

If cell.Offset(0, 1) = "Hi" Then
hicnt = hicnt + 1
prev = "Hi"
ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then
hicnt = hicnt + 1
End If
Next cell
Range("D1").Value = "Low Cnt " & locnt
Range("D2").Value = "Hi Cnt " & hicnt
End Sub

Charles

 

[smandula]

Thanks very much for your reply. The VBA works perfect on total.

Sorry to say I need the number or count between grouping. For instance
Low+blank=2 next item Hi+blank+blank=3 next item
Low+blank+blank+blank=4 next item Hi =1 next item Hi+blank+lank=3

Could this grouping be possible?
Thank Again

 

[Chris Ferguson]

How about a variation on Jonathan's solution.

In 'D' D2 = if (C2<>"",D1+1,1)
This looks at column 'C' and 'counts' till it finds another entry in 'C'

In 'E' E2 if (C3 <> "" ,D2,"")
This looks at column C and displays the 'count' just before there is an
entry in 'C'

This any use?


Chris

 

[Charles Harmon]

Hi,

Lets try this:

Sub HiLowCnt()
Dim cell As Range, rng As Range
Dim prev
Dim hicnt As Integer, locnt As Integer
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
prev = "Low"
For Each cell In rng
If cell.Offset(0, 1) = "Low" Then
If prev = "Hi" Then
cell.Offset(-hicnt, 3) = "Hi Cnt " & hicnt
hicnt = 0
End If
locnt = locnt + 1
prev = "Low"
ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then
locnt = locnt + 1
ElseIf cell.Offset(0, 1) = "Hi" Then
If prev = "Low" Then
cell.Offset(-locnt, 3) = "Low Cnt " & locnt
locnt = 0
End If
hicnt = hicnt + 1
prev = "Hi"
ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then
hicnt = hicnt + 1
End If
If prev = "Hi" And cell.Offset(1, 0) = "" Then
cell.Offset(, 3) = "Hi Cnt " & hicnt
hicnt = 0
End If

If prev = "Low" And cell.Offset(1, 0) = "" Then
cell.Offset(, 3) = "Low Cnt " & locnt
locnt = 0
End If
Next cell
End Sub
There are other and better ways, but ......

Charles

 

[smandula]

That's exactly it!!

Many Thanks
The Macro is elegant.

With Thanks
Steve