VBA count instances based on grouped rows

[ReJig]

Is it possible to use VBA to calculate the number of numerical occurance within a column of data, and then categorise the results (onto another sheet) based on a grouping variable found in another column of data? I've been trying to create a macro to do such a thing, but I'm not even close

To help understand, this is an example of a data sheet

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 117px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Name</TD><TD>email.address</TD><TD>affiliation</TD><TD>var1</TD><TD>var2</TD><TD>var3</TD><TD>var4</TD><TD>var5</TD><TD>Score1</TD><TD>Score2</TD><TD>Score3</TD><TD>Score4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Name1</TD><TD>email1</TD><TD>UnitC</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Name2</TD><TD>email2</TD><TD>UnitW</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Name3</TD><TD>email3</TD><TD>UnitL</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Name4</TD><TD>email4</TD><TD>UnitL</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Name5</TD><TD>email5</TD><TD>UnitW</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">24</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Name6</TD><TD>email6</TD><TD>UnitL</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">31</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Name7</TD><TD>email7</TD><TD>UnitW</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Name8</TD><TD>email8</TD><TD>UnitC</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Name9</TD><TD>email9</TD><TD>UnitW</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Name10</TD><TD>email10</TD><TD>UnitL</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I would like to use VBA to count the number of values less than 10 across four columns (Score1, Score2, Score3, and Score4) and then place the results in another sheet to look like this (i.e., according to the 'grouping labels' in Column C). The grouping labels will probably change from time to time, so one couldn't know in advance what to specify for grouping.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 114px"><COL style="WIDTH: 103px"><COL style="WIDTH: 106px"><COL style="WIDTH: 99px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>affiliation</TD><TD>Score1_less_10</TD><TD>Score2_less_10</TD><TD>Sscore3_less_10</TD><TD>Score4_less_10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>UnitC</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>UnitW</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>UnitL</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Is this possible?

 

[Legacy 68668]

The problem is your data layouts.

It only needs to loop Col.C and last 4 columns.
It those columns of data is isolated, should have no problem, but in your case,
it is too hard for the code to determine which columns to be anyalised.

 

[ReJig]

Hmmm...I see. Thank you for the response. Perhaps seperate macros would have to be created for different data layouts. Suppose, for example, the four colums to be isolated in one data layout were FF, FG, FH, FI and the column to be looped was Column G, what elements of the code would have to be changed?

Forgive me if this question does not properly recognize the argument in your last response.

 

[Legacy 68668]

try this

Sub test()
Dim rng As Range, a, b, c(), i As Long, ii As Long, myNum As Double, n As Long
Set rng = Application.InputBox("Select affiliation data range", type:=8)
It rng Is Nothing Then Exit Sub
a = rng.Value : Set rng = Nothing
Set rng = Application.InputBox("Select Score data range", type:=8) 
If rng Is Nothing Then Exit Sub
b = rng.Resize(UBound(a, 1), 4).Value
myNum = Application.InputBox("Enter the number to be categorised", type:=1)
ReDim c(1 To UBound(a, 1), 1 To 5)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            n = n + 1 : c(n, 1) = a(i, 1) : .item(a(i, 1)) = n
        End If
        For ii = 1 To 4
            If (b(i, ii) > 0) * (b(i, ii) < myNum) Then
                c(.item(a(i, 1)), ii + 1) = c(.item(a(i, 1)), ii + 1) + 1
            End If
        Next
    Next
End With
With Sheets("sheet2").Cells(1)
    .Resize(, 5).Value = Array("affilication", "Score1_less_" & myNum, _
          "Score2_less_" & myNum, "Score3_less_" & myNum, "Score4_less_" & myNum)
    With .Offset(1).Resize(n, 5)
        .Value = c
        On Error Resume Next
        .SpecialCells(4).Value = 0
    End With
End With
End Sub

 

[ReJig]

Thanks a lot. Seems to work very well; I'll test more when I have more time.

 

[beelzebubble]

Hi,

I've tested the last macro posted in this thread a little more and found that it does indeed work for my purposes. I've been trying, though, to modify the code such that instead of the user selecting the range via:

Set rng = Application.InputBox("Select affiliation data range", type:=8)

they instead simply input the column number to identify the range (minus the header). But I haven't had any success.

Any help on this matter is greatly appreciated.