Counting groups of similar items

[Jason]

Hi Folks. I have a row of labels. These labels are in groups (one
label for each hour that a particular product spec is being run, in
case you were interested). I would like to count the number of cells
that each group takes up e.g.

Row 1: a a a a b b b b b c c c a a a a c c c c c c c...
Row 2: 1 2 3 4 1 2 3 4 5 1 2 3 1 2 3 4 1 2 3 4 5 6 7...

Row 2 is calculated and shows that the first run of ‘a' goes for 4
hours followed by a run of ‘b' for 5 hours and then another 4 hour run
of ‘a' etc etc I thought I was getting close to formula solution using
offset, match and countif, but ran into problems when the same label
was used later down the line (like ‘a' in the example).

A more elegant solution would be if I could have a macro give me the
total run length for the spec that was in the current active cell .
Say I was on a ‘b' the macro would pop up a 5 somewhere, telling me
that that run is planned for 5 hours. (in reality I'll be looking up
the max run length for that spec and displaying it minus the 5 hours
that are planned for that run).

Anyway, I hope that's enough to explain what I am trying to achieve.

Any input is appreciated, thanks,
Jason.

 

[Frank Kabel]

Hi
try using COUNTIF
e.g.
=COUNTIF(1:1,"b")
to get '5' as a result

 

[Bob Phillips]

Jason,

Why not just use

=COUNTIF(A1:M1,"b")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JWolf]

Use the array formula:

=MAX(--(A1:IV1=C1)*(A2:IV2))

enter in cell C2 and hit ctrl+shift+enter

enter a ,b, c, etc. in C1 and the maximum run length is calculated for
that letter.

 

[JWolf]

Use the array formula:

=MAX(--(A1:IV1=C1)*(A2:IV2))

enter in cell C2 and hit ctrl+shift+enter

enter a ,b, c, etc. in C1 and the maximum run length is calculated for
that letter.

 

[Jason]

Looks like I've made the problem sound a lot easier than it is...
Thanks for all the suggestions so far, but countif on it's own is
definitely not going to work.

I would like to count the number of items in each group. HOWEVER,
because the same spec RE-OCCURS further down the line the countif
statement will include that, whereas I only want to know how many are
in the CURRENT group. Countif in the example below would give a value
of 10 for ?c? when I would like to have it return a value of 3 for the
first group of ?c? and then 7 for the second group of ?c?.

The rows 2 or 3 in my example shows the bare bones of what I would
like to be able to get. Since this is in the programming group I was
hoping to get some assistance towards achieving that more elegant
solution I mentioned where a mouse click on the group would indicate
the total length of that group.

Examples again:

Row 1: a a a a b b b b b c c c a a a a c c c c c c c...
Row 2: 1 2 3 4 1 2 3 4 5 1 2 3 1 2 3 4 1 2 3 4 5 6 7...
Row 3: 4 4 4 4 5 5 5 5 5 3 3 3 4 4 4 4 7 7 7 7 7 7 7...

Row 1 is the row of data I have (dynamic), Rows 2 and 3 show what I
would like to be able to have a formula calculate if the ?elegant?
method is not achievable.

Thanks again,
Jason.

 

[Jason]

I'm having no luck with the array formula - Entering it in C2 gives a
circular reference error. Not fully understanding how it works I'm
not having a lot of luck correcting it, and when I do get something
that almost resembles working it doesn't like non-numeric data...

 

[Jason]

I'll have another shot at explaining my scenario - hopefully as
clearly and concisely as possible this time...

Below is the example data set; each letter is in a separate column:

A A A B B B B B A A A A A A A A C C C C...ZCA ZCA ZCA

What I would like to have is a popup when I click on any cell in a
group telling me how long that group is.

So the result for the row above would tell me that the first group of
A is 3 long, the group of B is 5 long, the second group of A is 8
long, the group of C is 4 long, the group of ZKV is 3 long.

I'd even settle for being able to get row below that tells me how long
each group is (as per my examples in other messages in this thread).

I hope someone can get his or her head around this...

 

[Jason]

I finally found a thread that outlines close to what I am after.
http://groups.google.com/groups?hl=...4c&[email protected]

I've buggerised the code to get something that works fairly well
(below). Although I'd prefer to have it only tell me what the length
is of the group around the current cell, instead of all the groups in
the row. I'm also sure that there's a cleaner way of doing the "Case"
part of the code, but with my current skill level I'm not entirely
certain how to do it...

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)

Dim r, x_range, SearchRow As Range
Dim i As Long
Dim msg As String

Select Case Target.Row
Case "3"
Set SearchRow = Range("3:3")
Case "5"
Set SearchRow = Range("5:5")
Case "7"
Set SearchRow = Range("7:7")
Case "9"
Set SearchRow = Range("9:9")
Case Else
Exit Sub
End Select

msg = ""
For Each r In SearchRow
If r = Selection Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & Selection & " run " & i & ": " &
x_range.Areas(i).Cells.Count & vbCrLf
Next i

MsgBox msg

Cancel = True

End Sub

 

[TroyW]

Jason,

Does this do what you want? Place the code in the "BeforeRightClick" event
procedure of the worksheet code module.

Troy


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim rng As Range
Dim iOffset As Integer
Dim iLeft As Integer
Dim iRight As Integer
Dim iTotal As Integer

Set rng = ActiveCell

'''Count the number of similar cells that are to the left.
iOffset = 0
Do While rng.Offset(0, -iOffset).Value = rng.Value
iOffset = iOffset + 1
If rng.Column - iOffset < 1 Then Exit Do
Loop
iLeft = iOffset - 1
'MsgBox "Left = " & iLeft

'''Count the number of similar cells that are to the right.
iOffset = 0
Do While rng.Offset(0, iOffset).Value = rng.Value
iOffset = iOffset + 1
If rng.Column + iOffset > 256 Then Exit Do
Loop
iRight = iOffset - 1
'MsgBox "Right = " & iRight

'''Report the total number of similar cells.
iTotal = iLeft + iRight + 1
MsgBox rng.Value & vbCr & vbCr & "Count = " & iTotal

'''Cancel the popup menu.
Cancel = True
End Sub

 

[Jason]

Excellent... Thank you very much Troy, that's exactly what I was after.
A lot tidier than my attempt.

 

[Jason]

As I mentioned Troy's solution is excellent. But in the quest for
continuous improvement I'm wondering if there is there a way I can
modify it so that it shows a 'tooltip' (a popup with no 'ok' button)
showing the total run length for the currently selected cell? I know
how to constrain it to a range, so basically all I need to know is if
it's possible to get a small popup that only shows for the currently
selected cell and doesn't need acknowledging... (I've already scoured
the newsgroup for info on popups and tooltips, but haven't had a lot
of success).