Count subset-totals in column

  • Thread starter Thread starter Pluggie
  • Start date Start date
P

Pluggie

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
 
Pluggie said:
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.
Click to expand...

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 said:
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)),"")
Click to expand...


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!
 
Back
Top