hi all€¦. Im trying to count the number of times a name is on consecutive
cells. Sounds easy but I can't find a function for this, maybe a little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.

--
Thank you...

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on consecutive
cells. Sounds easy but I can't find a function for this, maybe a little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.

--
Thank you...

Sorry Bob, it does not work. You can try it by changing the places for
example:

A1 = blank
A2 = AAA
A3 = AAA
A4 = AAA
A5 = blank
A6 = AAA

Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and
group 2 for a6)
Thank youf or replying, any other ideas?
--
Thank you...


"Bob Phillips" wrote:

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on consecutive
cells. Sounds easy but I can't find a function for this, maybe a little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.

--
Thank you...


.

I didn't allow a for row 1 being blank

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
Sorry Bob, it does not work. You can try it by changing the places for
example:

A1 = blank
A2 = AAA
A3 = AAA
A4 = AAA
A5 = blank
A6 = AAA

Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and
group 2 for a6)
Thank youf or replying, any other ideas?
--
Thank you...


"Bob Phillips" wrote:

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on
consecutive
cells. Sounds easy but I can't find a function for this, maybe a
little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.

--
Thank you...


.

Hi Bob, sorry if i'm not explaining the situation.... your formula does not
work if we change the scenario, I did,

A1 = Blank
A2 = Blank
A3 = Blank
A4 = AAA
A5 = blank
A6 = AAA

and your formula gives me a 4, i'm expecting a 2. Just to give you more
information, each cell is a day of the month and the name is a person who
will perform a dutie. I want to know at the end of the month how many times
1 person did two or more days in a row. At the end it could be random so a
person might perform a function 3 days in a row and I want to count that as 1
frequency.... make sense?

and I expct
--
Thank you...


"Bob Phillips" wrote:

I didn't allow a for row 1 being blank

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
Sorry Bob, it does not work. You can try it by changing the places for
example:

A1 = blank
A2 = AAA
A3 = AAA
A4 = AAA
A5 = blank
A6 = AAA

Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and
group 2 for a6)
Thank youf or replying, any other ideas?
--
Thank you...


"Bob Phillips" wrote:

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on
consecutive
cells. Sounds easy but I can't find a function for this, maybe a
little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.

--
Thank you...


.



.

Somehow, I can't help feeling you have simplified your question for this
newsgroup and the your real question is more complex than this. Is a UDF
(user defined function) solution okay? If so, then the following UDF will do
what your original posting asked for, namely, count groupings of the "name"
AAA in the Column 1 (although I did design the UDF to allow you to specify
an alternate column is desired)...

Function CountAAAGroups(Optional Col As Variant = "A") As Long
On Error Resume Next
CountAAAGroups = 1 + UBound(Split(WorksheetFunction.Trim(Replace(Join( _
WorksheetFunction.Transpose(Intersect(Columns(Col) , _
ActiveSheet.UsedRange))), "A ", "A"))))
End Function

If you have never worked with UDFs, you would install the above by pressing
Alt+F11 to go to the Visual Basic editor, then click Insert/Module from its
menu bar and copy/paste the above code into the code window that opened up.
To use the UDF, just call it like any other Excel worksheet function; for
example, enter this in any cell...

=CountAAAGroups()

If you wanted to specify a column other than Column A, you can do so one of
two ways (let's use Column E as our example); like this...

=CountAAAGroups("E")

or like this

=CountAAAGroups(5)

--
Rick (MVP - Excel)


"EXCELMACROS" wrote in message
...
Hi Bob, sorry if i'm not explaining the situation.... your formula does
not
work if we change the scenario, I did,

A1 = Blank
A2 = Blank
A3 = Blank
A4 = AAA
A5 = blank
A6 = AAA

and your formula gives me a 4, i'm expecting a 2. Just to give you more
information, each cell is a day of the month and the name is a person who
will perform a dutie. I want to know at the end of the month how many
times
1 person did two or more days in a row. At the end it could be random so
a
person might perform a function 3 days in a row and I want to count that
as 1
frequency.... make sense?

and I expct
--
Thank you...


"Bob Phillips" wrote:

I didn't allow a for row 1 being blank

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
Sorry Bob, it does not work. You can try it by changing the places for
example:

A1 = blank
A2 = AAA
A3 = AAA
A4 = AAA
A5 = blank
A6 = AAA

Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4
and
group 2 for a6)
Thank youf or replying, any other ideas?
--
Thank you...


"Bob Phillips" wrote:

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on
consecutive
cells. Sounds easy but I can't find a function for this, maybe a
little
macro that goes cell by cell on each row? I appreciate your help,
for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in
two
groups, first group 2 times and second group 3 times.

--
Thank you...


.



.

hi, (?)

... trying to count the number of times a name is on consecutive cells... example:
A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA
Then my results should be equal to 2 since the name AAA appear in two groups
first group 2 times and second group 3 times...

(i.e.) =sumproduct(--(a1:a6="aaa"),--(a1:a6<a2:a7))

hthm
hector.

Every time your examples change the requirement. If you want a full
solution, give us a full requirement.

Bob

"EXCELMACROS" wrote in message
...
Hi Bob, sorry if i'm not explaining the situation.... your formula does
not
work if we change the scenario, I did,

A1 = Blank
A2 = Blank
A3 = Blank
A4 = AAA
A5 = blank
A6 = AAA

and your formula gives me a 4, i'm expecting a 2. Just to give you more
information, each cell is a day of the month and the name is a person who
will perform a dutie. I want to know at the end of the month how many
times
1 person did two or more days in a row. At the end it could be random so
a
person might perform a function 3 days in a row and I want to count that
as 1
frequency.... make sense?

and I expct
--
Thank you...


"Bob Phillips" wrote:

I didn't allow a for row 1 being blank

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
Sorry Bob, it does not work. You can try it by changing the places for
example:

A1 = blank
A2 = AAA
A3 = AAA
A4 = AAA
A5 = blank
A6 = AAA

Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4
and
group 2 for a6)
Thank youf or replying, any other ideas?
--
Thank you...


"Bob Phillips" wrote:

Try

=COUNTIF(A1:A6,"<AAA")+(A6="AAA")

HTH

Bob

"EXCELMACROS" wrote in message
...
hi all.. I'm trying to count the number of times a name is on
consecutive
cells. Sounds easy but I can't find a function for this, maybe a
little
macro that goes cell by cell on each row? I appreciate your help,
for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in
two
groups, first group 2 times and second group 3 times.

--
Thank you...


.



.

I created a function below that should give you what you want.
You need to give the function a row value ("iRow"), how
many cells or columns to count ("n"), and tell it which
name or string to search for ("token").

So, you could use it like follows:

MsgBox "Total AAA's in Row1 a " & CountRowTokens(1, 6, "AAA")

Since it's also a function, you could probably use it as a formular in
the actual spreadsheet as well.

================================================== ======================
Public Function CountRowTokens(ByVal iRow As Integer, ByVal n As Integer,
ByVal token As String)

Dim i, cState, iState, nFull As Integer

If (Cells(iRow, 1).Value = token) Then
nFull = 1
cState = 1
Else
nFull = 0
cState = 0
End If

For i = 2 To n
If (Cells(iRow, i).Value = token) Then
iState = 1
Else
iState = 0
End If

If (iState < cState) Then
If (iState = 0) Then
cState = 0
Else
cState = 1
nFull = nFull + 1
End If

End If
Next i

CountRowTokens = nFull

End Function
================================================== ===========

Thank you Robert, Hector, Bob and Rick, I'm going to test out your options
tonight and will give you the results tomorrow. SORRY for not being more
specific and yes, I'm familiar with VB.
--
Thank you...


"Robert Crandal" wrote:

I created a function below that should give you what you want.
You need to give the function a row value ("iRow"), how
many cells or columns to count ("n"), and tell it which
name or string to search for ("token").

So, you could use it like follows:

MsgBox "Total AAA's in Row1 a " & CountRowTokens(1, 6, "AAA")

Since it's also a function, you could probably use it as a formular in
the actual spreadsheet as well.

================================================== ======================
Public Function CountRowTokens(ByVal iRow As Integer, ByVal n As Integer,
ByVal token As String)

Dim i, cState, iState, nFull As Integer

If (Cells(iRow, 1).Value = token) Then
nFull = 1
cState = 1
Else
nFull = 0
cState = 0
End If

For i = 2 To n
If (Cells(iRow, i).Value = token) Then
iState = 1
Else
iState = 0
End If

If (iState < cState) Then
If (iState = 0) Then
cState = 0
Else
cState = 1
nFull = nFull + 1
End If

End If
Next i

CountRowTokens = nFull

End Function
================================================== ===========

.

On Fri, 5 Feb 2010 04:20:01 -0800, EXCELMACROS
wrote:

hi all…. I’m trying to count the number of times a name is on consecutive
cells. Sounds easy but I can't find a function for this, maybe a little
macro that goes cell by cell on each row? I appreciate your help, for
example:

A1 = AAA
A2 = AAA
A3 = blank
A4 = AAA
A5 = AAA
A6 = AAA

Then my results should be equal to 2 since the name AAA appear in two
groups, first group 2 times and second group 3 times.


Try this formula:

=SUMPRODUCT(--((A1:A6)="AAA"),--((A2:A7)<"AAA"))

Hope this helps / Lars-Åke