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]

Sub test()
Dim a, b(), i As Long, ii As Long, n As Long
With Sheets("sheet1")
    a = .Range("c2", .Range("c" & Rows.Count).End(xlUp)).Resize(, 10).Value
End With
ReDim b(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 : b(n, 1) = a(i, 1) : .item(a(i, 1)) = n
        End If
        For ii = 7 To 10
            If a(i, ii) < 10 Then
                b(.item(a(i, 1)), ii - 5) = b(.item(a(i, 1)), ii - 5) + 1
            End If
        Next
    Next
End With
With Sheets("sheet2").Cells(1)
    .Resize(, 5).Value = Array("affilication", "Score1_less_10", _
          "Score2_less_10", "Score3_less_10", "Score4_less_10")
    .Offset(1).Resize(n, 5).Value = b
End With
End Sub

 

[ReJig]

Seiya: I only now got around to testing your suggested code. It works very well. Thank you very much.

May I ask, how can the code be modified to include a value of '0' in 'sheet2' for those instanced where there are zero identified cases that satisfy the condition (e.g., <10). Currently, it simply leaves a blank space in the cell.

Finally, with what would one replace '<10' if one wanted to count the number of instances that occur between a range (e.g., 0 to .99999)?

thanks again.

 

[Legacy 68668]

Do you want to categorise like

Score1_less_0.9999
Score1_less_10
Score1_less_100
Score1_less_1000

According to the header ?

 

[ReJig]

Ah, I don't think so.

Suppose the values within the Score1, Score2, Score3, Score4 columns ranged from 0 to 5 (some values might be 0.58, for example). Instead of counting the number of instances of values less than 10 within these four columns, the macro would count the number of instances of values between 0 and .999 (rather than <10, as in the current macro).

 

[Legacy 68668]

try

Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, myNum As Double
myNum = Application.InputBox("Enter the number to be categorised", type:=1)
With Sheets("sheet1")
    a = .Range("c2", .Range("c" & Rows.Count).End(xlUp)).Resize(, 10).Value
End With
ReDim b(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 : b(n, 1) = a(i, 1) : .item(a(i, 1)) = n
        End If
        For ii = 7 To 10
            If a(i, ii) < myNum Then
                b(.item(a(i, 1)), ii - 5) = b(.item(a(i, 1)), ii - 5) + 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 = b
        On Error Resume Next
        .SpecialCells(4).Value = 0
    End With
End With
End Sub

 

[ReJig]

You're suggestion works very well. However, I failed to mention that, from time to time, the Score1, Score2, Score3, Score4 columns of data may have a value of -100 to demarcate a missing value. Consequently, simply using a 'less than' operator may produce misleading results. So, I think I'm kinda stuck with having to specify two criteria: one low (0) and one high (.99999).

 

[Legacy 68668]

OK
change

            If a(i, ii) < myNum Then

to

            If (a(i, ii) > 0) * (a(i, ii) < myNum) Then

 

[ReJig]

Does the job perfectly. Thanks!

 

[ReJig]

I've been playing around with the macro to figure out its versatility and how it works (I have limited VBA experience). I can't seem to figure out specifically which elements within the macro have to be changed to accomodate a greater number of columns in between the 'grouping column' (in this case Column C) and the 4 columns which contain the data to be examined for counting. That is, suppose there were 20 columns of 'var' variables in between Column C and the Column headed Score1, what paramaters within the code need to be changed (and to what)?

I'm thinking it is

a = .Range("c2", .Range("c" & Rows.Count).End(xlUp)).Resize(, 10).Value

and

For ii = 7 To 10

but a decent amount of trial and error testing hasn't shown me the way