CODEWITHSUNDEEP
excelvba
Andre Silva
Andre Silva

Reputation: 4928

Counting number of specific observations inside groups of rows

Reproducible example

Consider the following data:

ID  ID_2    Specie          Area    Tree    DBH      H      Cod
2   111     E_citriodora    432     1       19.098   20 
2   111     E_citriodora    432     2                       1
2   111     E_citriodora    432     3                       1
2   111     E_citriodora    432     4       20.530   17.4   6
...
2   111     E_grandis       557     1                       1
2   111     E_grandis       557     2       24.828   15     6
2   111     E_grandis       557     3                       1
2   111     E_grandis       557     4       14.483   16     5
...
2   111     E_paniculata    704     1                       1
2   111     E_paniculata    704     2       14.164   19.5   
2   111     E_paniculata    704     3                       1
2   111     E_paniculata    704     4       17.507   20

Here is a complete reproducible example with 208 rows. The actual data has more rows and species, in which the number of rows per specie is not always the same.

Question

What I would like to do is the following:

Check if the count of code 6 on column "Cod" for each specie is smaller than 3 (minimum threshold) and greater than Area/100 (considering the result rounded up to an integer). If one of the conditions are met, I would like to display a message box.

Count of code 6 is smaller than 3 or greater than roundup(Area/100,0)

Expected result

E_citriodora has four numbers 6 on column "Cod". The correct count of code number 6 should be between 3 and =ROUNDUP(432/100,0)=5. So, 3 < 4 < 5 would not trigger the message box.

E_grandis has seven observations for code 6, but in this case the maximum threshold is 6 because the area of 557/100 is 5.57 which rounded up is 6.
3 < 7 < 6. This result would trigger the message box.

The third example, E_paniculata has only 2 observations for code 6. This is smaller than the minimum threshold of 3. 3 < 2 < 8. This result would also trigger the message box.

It is not necessary to display a message box for each time a condition is met, but just one message indicating there is at least one flaw.

What I have tried

I could do this manually for each specie using formulas. For example, regarding the first specie of the data frame:

=IF(OR(COUNTIF(H2:H73,6) < 3,COUNTIF(H2:H73,6) > ROUNDUP(D2/100,0)),"Not Ok", "Ok")

However I was expecting to achieve this with a macro and my main difficulty has been to set the count inside each group of specie and which type of loop would be the most suitable in this situation. Tks.

Upvotes: 0

Views: 238

Answers (1)

Roland
Roland

Reputation: 976

Assuming your data is always sorted the way in your example file, this code would print all species with code6 greater than 3 to your console:

Sub test()

    'Assuming A2 in Sheet 1 contains your first ID
    Dim r As Range
    Set r = ThisWorkbook.Sheets(1).Range("A2")
    if r = "" then exit sub

    Dim specie As String
    specie = ""

    Dim cod6 As Integer

    'Stop at first empty row
    Do While Not r = "" 

        'Next Specie
        If specie <> r.Offset(0, 2) Then
            specie = r.Offset(0, 2)
            cod6 = 0
        End If

        'Count cod
        If r.Offset(0, 7) = 6 Then cod6 = cod6 + 1
        'Check cod at end of specie
        If specie <> r.Offset(1, 2) Then

            'Put your real condition here and make a msgbox
            If cod6 > 3 Then Debug.Print specie & " has cod6 greater than"

        End If

        Set r = r.Offset(1, 0)
    Loop

End Sub

Upvotes: 1

Related Questions