I have a column with data.
The data consists of incremental numbers:

Column A

1
2
3
4

1
2

1
2
3

What I want to do in column B is count the number of instances of each subset.
Result would look like this:

Column A Column B
4
1
2
3
4
2
1
2
3
1
2
3

The largest subset consists of 9 instances.

I tried reaching a solution using CountIf and nested If-statements, but
without any luck. Anybody have any bright ideas?

by the way 1:
I do have the add-on morefucntions, but didn't recognize a usefull fuction
in there either).

by the way 2: I could replace the numbers with a single character if that
would help simplify this issue.

Thanks in advance for your help.

One way with a macro

Sub countinstancesbetweenblanks()
mc = "f"
lr = Cells(Rows.Count, mc).End(xlUp).Row
counter = 1
Do Until r2 lr
r1 = Columns(mc).Find(What:="*", after:=Cells(counter, mc),
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
r2 = Columns(mc).Find(What:="", after:=Cells(r1, mc), LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
MsgBox r2 - r1
Cells(r1, mc).Offset(, 1) = r2 - r1
counter = r2
Loop
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pluggie" wrote in message
...
I have a column with data.
The data consists of incremental numbers:

Column A

1
2
3
4

1
2

1
2
3

What I want to do in column B is count the number of instances of each
subset.
Result would look like this:

Column A Column B
4
1
2
3
4
2
1
2
3
1
2
3

The largest subset consists of 9 instances.

I tried reaching a solution using CountIf and nested If-statements, but
without any luck. Anybody have any bright ideas?

by the way 1:
I do have the add-on morefucntions, but didn't recognize a usefull fuction
in there either).

by the way 2: I could replace the numbers with a single character if that
would help simplify this issue.

Thanks in advance for your help.

Pluggie wrote:
I have a column with data.
The data consists of incremental numbers:

Column A

1
2
3
4

1
2

1
2
3

What I want to do in column B is count the number of instances of each subset.
Result would look like this:

Column A Column B
4
1
2
3
4
2
1
2
3
1
2
3

The largest subset consists of 9 instances.

I tried reaching a solution using CountIf and nested If-statements, but
without any luck. Anybody have any bright ideas?

by the way 1:
I do have the add-on morefucntions, but didn't recognize a usefull fuction
in there either).

by the way 2: I could replace the numbers with a single character if that
would help simplify this issue.

Thanks in advance for your help.


If there are now blank rows between subsets and the data starts in row 2, put
this in B2 and copy down (replace the 1000's with the number of rows in your data):

=IF(A2=1,IF(ISNUMBER(MATCH(1,A3:$A$1000,0)),
MATCH(1,A3:$A$1000,0),COUNT(A2:$A$1000)),"")

If there are blank rows between subsets, subtract 1 from the MATCH() result:

=IF(A2=1,IF(ISNUMBER(MATCH(1,A3:$A$1000,0)),
MATCH(1,A3:$A$1000,0)-1,COUNT(A2:$A$1000)),"")

Glenn wrote:
Pluggie wrote:
I have a column with data.
The data consists of incremental numbers:

Column A

1
2
3
4

1
2

1
2
3

What I want to do in column B is count the number of instances of each
subset.
Result would look like this:

Column A Column B
4
1 2 3
4 2
1 2 3
1 2 3
The largest subset consists of 9 instances.

I tried reaching a solution using CountIf and nested If-statements,
but without any luck. Anybody have any bright ideas?
by the way 1:
I do have the add-on morefucntions, but didn't recognize a usefull
fuction in there either).

by the way 2: I could replace the numbers with a single character if
that would help simplify this issue.

Thanks in advance for your help.


If there are now blank rows between subsets and the data starts in row
2, put this in B2 and copy down (replace the 1000's with the number of
rows in your data):

=IF(A2=1,IF(ISNUMBER(MATCH(1,A3:$A$1000,0)),
MATCH(1,A3:$A$1000,0),COUNT(A2:$A$1000)),"")

If there are blank rows between subsets, subtract 1 from the MATCH()
result:

=IF(A2=1,IF(ISNUMBER(MATCH(1,A3:$A$1000,0)),
MATCH(1,A3:$A$1000,0)-1,COUNT(A2:$A$1000)),"")


That first sentence should read "If there are NO blank rows..."

Assuming that A2:A12 contains the data, try...

B1:

=MATCH(9.99999999999999E+307,A:A)

B2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(A1="",IF(A2<"",COUNT(A2:INDEX(A2:INDEX(A:A,$B $1),LOOKUP(9.9999999999
9999E+307,CHOOSE({1,2},$B$1-ROW(B2)+1,MATCH(TRUE,A2:INDEX(A:A,$B$1)="",0)
-1)))),""),"")

Hope this helps!

In article ,
Pluggie wrote:

I have a column with data.
The data consists of incremental numbers:

Column A

1
2
3
4

1
2

1
2
3

What I want to do in column B is count the number of instances of each subset.
Result would look like this:

Column A Column B
4
1
2
3
4
2
1
2
3
1
2
3

The largest subset consists of 9 instances.

I tried reaching a solution using CountIf and nested If-statements, but
without any luck. Anybody have any bright ideas?

by the way 1:
I do have the add-on morefucntions, but didn't recognize a usefull fuction
in there either).

by the way 2: I could replace the numbers with a single character if that
would help simplify this issue.

Thanks in advance for your help.