Removing Data Points - Longitudinal Study

[bryleek]

Hi hello!
In column A I have my subject IDs and in column B I have the event name which is the time points they were seen at. Ideally, each subject ID was seen at all 3 time points (baseline, 1 year, 2 year) which is why the subject ID's will be listed multiple times, but it is okay if they were only seen at baseline and 2 year (missing 1 year). I am wondering how to remove the subjects who were not seen for at least baseline and 2 year (i.e. they only have 1 timepoint listed, or were seen at baseline and 1 year but not 2 year). I can't even begin to wrap my head around this so any help is greatly appreciated.

 

[kevin9999]

Is it the case that - if a subject has a "2 year" listed against them, then they should be retained, otherwise they should be deleted? If so, and assuming that you only have 2 columns of data (as per your image) starting from cell A1, then try the following on a copy of your data. Should work fine with a limited number of records, but would need something faster if you're looking at thousands.

Option Explicit
Sub Zap_no_2_Years()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    Dim r As Range, calc As Range
    Set r = ws.Range("A1").CurrentRegion
    Dim LRow As Long
    LRow = r.Rows.Count
    
    With r
        Set calc = .Offset(1, .Columns.Count).Resize(.Rows.Count - 1, 1)
        With calc
            .FormulaR1C1 = "=COUNTIFS(R2C1:R" & LRow & "C1,RC1,R2C2:R" & LRow & "C2,""2*"")"
        End With
        .Cells(1).AutoFilter 3, 0
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    calc.ClearContents
    Application.ScreenUpdating = True
End Sub