Find first TRUE value each week

[rongil20]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Week No
[/TD]
[TD]Match
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TRUE
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]TRUE
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]TRUE
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]TRUE
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]TRUE
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi, I am trying to find a way to get the third column in the above dataset to find the first TRUE value in each week (which I have manually done myself above).

Obviously I need a macro but no idea where to even begin with this, any help much appreciated.

Thanks

 

[mumps]

Try:

Sub findTrue()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim rngUniques As Range
    Dim foundVal As Range
    Sheets("Sheet1").Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("A1:A" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    For Each rng In rngUniques
        Range("A1:C" & LastRow).AutoFilter Field:=1, Criteria1:=rng
        Set foundVal = Range("B:B").SpecialCells(xlCellTypeVisible).Find("TRUE")
        If Not foundVal Is Nothing Then
            foundVal.Offset(0, 1) = "YES"
        End If
    Next rng
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = False
End Sub

 

[rongil20]

This is very good, thanks

 

[Tetra201]

... Obviously I need a macro...

Will a formula work for you?
If yes, try placing the following formula in cell C3 and drag-copying it down as needed

=IF(AND(B2,COUNTIFS($A$2:A2,A2,$B$2:B2,TRUE)=1),"YES","")