Getting all the information to be shown during the search

[Dana2020]

Hi
I have a file where you can find 2 sheets ( Data and Interface ) The datasheet the user must enter the data in the sheet. In the other sheet (Interface) I applied Advance Filter to make the search easier for the user to find the required information (Detect the patients returned to the Emergency Department within 72 hours). Is there a method to make the user search for the information and to get all the visits for the patient and which the visit within 72 hours?

[nankw83]

Hi Dana,

Try below code, for calculating the 72 hours the code uses your current time Now() and the date/time of Date of ER Visit & Registration Time

Sub Test()

Dim Lc As Long, Lr As Long, Rg As Range
Set Rg = Sheet2.[A1:A2]
Rg = [{"Flag";1}]

With Sheet1
   Lc = .Cells(1, Columns.Count).End(1).Column + 1
   Lr = .Cells(Rows.Count, 1).End(3).Row
   .Cells(1, Lc).Resize(Lr) = Evaluate("if(now()-3<" & .[B1].Resize(Lr).Address(, , , True) _
         & "+" & .[C1].Resize(Lr).Address(, , , True) & ",1,"""")")
   .Cells(1, Lc) = "Flag"
   .[A1].CurrentRegion.AdvancedFilter 2, Rg, Sheet2.[F11:H11]
   .Cells(1, Lc).Resize(Lr).ClearContents
End With

Rg.ClearContents

End Sub

[Dana2020]

I need to show all the records of the patients who returned to ER within 72 hours and also their previous visits. When I make a filter to the patients who returned to ER, what I get is only one record for each patient without their previous visits

[nankw83]

Try below code ...

Sub Test()

Dim Lc As Long, Lr As Long, Rg As Range, j$
Set Rg = Sheet2.[A1:A2]
Rg = [{"Flag";1}]

With Sheet1
   Lc = .Cells(1, Columns.Count).End(1).Column + 1
   Lr = .Cells(Rows.Count, 1).End(3).Row
   .Cells(1, Lc).Resize(Lr) = Evaluate("if(now()-3<" & .[B1].Resize(Lr).Address(, , , True) _
         & "+" & .[C1].Resize(Lr).Address(, , , True) & ",1,"""")")
   j = Split(.Cells(1, Columns.Count).End(1).Address, "$")(1)
   .Cells(1, Lc + 1).Resize(Lr).Formula = "=SUMIF(A:A,A1," & j & ":" & j & ")"
   .Cells(1, Lc + 1) = "Flag"
   .[A1].CurrentRegion.AdvancedFilter 2, Rg, Sheet2.[F11:H11]
   .Cells(1, Lc).Resize(Lr, 2).ClearContents
End With

Rg.ClearContents

End Sub

[Dana2020]

The code doesn't work

[nankw83]

What happens when you run the code ? Does it give any error ? If you're trying in the sample file you posted in post #1, you need to change some of the dates as all of them are more than 72 hours

[Dana2020]

When I run the code, the results are not what I want.
I don,t need to change any dates
There is a formula already in Column( O ), it will show if the patient returned within 72 hours
What I want is to show all the records for the patients who returned within 72 hours plus their previous visits, Because when I make a filter for the patients returned, it will show only one record for each patient

[nankw83]

There is a formula already in Column( O ), it will show if the patient returned within 72 hours

This wasn't clear to me ... Try below code

Sub Test()

Dim Lc As Long, Lr As Long, Rg As Range, j$
Set Rg = Sheet2.[A1:A2]
Rg = [{"Flag";">0"}]

With Sheet1
   Lc = .Cells(1, Columns.Count).End(1).Column + 1
   Lr = .Cells(Rows.Count, 1).End(3).Row
   j = Split(.Cells(1, Columns.Count).End(1).Address, "$")(1)
   .Cells(1, Lc).Resize(Lr).Formula = "=IF(ROW()=1,""Flag"",COUNTIFS(A:A,A1," & j & ":" & j & ",""Yes""))"
   .[A1].CurrentRegion.AdvancedFilter 2, Rg, Sheet2.[F11:H11]
   .Cells(1, Lc).Resize(Lr).ClearContents
End With

Rg.ClearContents

End Sub

[jindon]

Dana2020,

Try

Sub Search()
    Dim r As Range
    Sheets("interface").[f11].CurrentRegion.Offset(1).Clear
    With Sheets("data")
        .[aa2].Formula = "=and(b2>='interface'!$c$5,b2<='interface'!$d$5,o2=""Yes"")"
        .Cells(1).CurrentRegion.AdvancedFilter 2, .[aa1:aa2], Sheets("interface").[f11].CurrentRegion
        .[aa2].Clear
    End With
End Sub

[Dana2020]

These codes do not give me the results I want

[jindon]

Bad luck. .