Check if value in a cell is 'yes' within a group-CodePudding

example: I would like to know with vba if everyone in a year paid. So did everyone in year 1, year 2 etc pay?

payed	student	year
yes	smith	1
yes	jackson	1
no	ferral	2
no	hamilton	3
yes	jenner	1
no	west	2
yes	sullivan	2

result:

payed	student	year	year-completely-paid
yes	smith	1	yes
yes	jackson	1	yes
no	ferral	2	no
no	hamilton	3	no
yes	jenner	1	yes
no	west	2	no
yes	sullivan	2	no

All students in year 1 have 'yes' in year-completely-paid because all these students payed.

CodePudding user response：

VBA solution using a dictionary to get any no for the year.

    Dim i As Long
    Dim lr As Long
    Dim dict As Object
    Dim currentyear As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    With ActiveSheet
        lr = .Cells(.Rows.Count, 1).End(xlUp).row
        
        For i = 2 To lr
            currentyear = .Cells(i, 3).Value
            If dict.exists(currentyear) Then
                If dict(currentyear) = "yes" And .Cells(i, 1).Value = "no" Then
                    dict(currentyear) = "no"
                End If
            Else
                dict.Add currentyear, .Cells(i, 1).Value
            End If
        Next
        
        For i = 2 To lr
            currentyear = .Cells(i, 3).Value
            .Cells(i, 4).Value = dict(currentyear)
        Next i
    End With

Formula solution:

=IF(COUNTIFS(C$2:C$8,C3,A$2:A$8,"no") > 0,"no","yes")

Change the ending row as needed.