Help! Not sure if this is possible with VBA??

[Lola]

My issue is this: I have a table that I need to pull some data from [filter, extract some values, and do some basic counting (sample with dummy data attached)].

Specifically, I need to identify the unique values in the ID column, count the number of "encounters" per unique ID (especially those with more than 3), find the most recent encounter and extract values from the other columns corresponding to that encounter for other basic functions (e.g. "for most recent visit, did patient have a PCP yes or no").

I took a VBA course around 10 years ago during graduate school but I'm afraid I've forgotten too much to figure this out quickly (I've tried). Is there anyone who can tell me #1) if this is feasible using VBA and #2) how to do it...?

I'll take help from anyone willing to offer it!

 

[Marc L]

Hi !

Could be done using VBA but maybe also without ! Just using formulas …
So why this VBA need ?

Must also precise or join a desired output …
​

 

[Lola]

This has to be done on many long monthly reports. I guess I assumed VBA would be cleaner (also, full disclosure, I was having a little trouble with the formulas too )

 

[Chihiro]

Got up to Latest entry and Count of Client on VBA.

Created dynamic named range and formula for rest. Couldn't wrap my head around scripting.dictionary with array.

Sub GetMaxDate()
Dim objDict As Object
Set objDict = CreateObject("Scripting.Dictionary")
With objDict
    .CompareMode = BinaryCompare
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If .Exists(Range("A" & i).Value) Then
        If .Item(Range("A" & i).Value) < Range("B" & i).Value Then .Item(Range("A" & i).Value) = Range("B" & i).Value
        Else
            .Add Range("A" & i).Value, Range("B" & i).Value
        End If
    Next i
Sheet2.Range("A2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
Call UniqueReport
End With
End Sub

Sub UniqueCount()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant
Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

varray = Range("A2:A" & lRow).Value

For Each element In varray
    If dict.Exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next

Sheet2.Range("C2").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.Items)

End Sub

 

[Lola]

My issue is this: I have a table that I need to pull some data from [filter, extract some values, and do some basic counting (sample with dummy data attached)].

Specifically, I need to identify the unique values in the ID column, count the number of "encounters" per unique ID (especially those with more than 3), find the most recent encounter and extract values from the other columns corresponding to that encounter for other basic functions (e.g. "for most recent visit, did patient have a PCP yes or no").

I took a VBA course around 10 years ago during graduate school but I'm afraid I've forgotten too much to figure this out quickly (I've tried). Is there anyone who can tell me #1) if this is feasible using VBA and #2) how to do it...?

I'll take help from anyone willing to offer it!

For Unique clients, here are the actual counts I need after all of the filtering:
# of clients defined as frequent users (3+ Encounters)
# of clients without a PCP/medical home
# of clients who have used the ER in the past 90 days
# of clients who have used the ER in the past 90 days for alcohol related substance abuse
# of clients who have used the ER in the past 12 months
# of clients who have used the ER in the past 12 months for alcohol related substance abuse
# of clients that are on Medicaid
# of clients that are Low Income
# of clients with No Insurance

 

[Lola]

you are AWESOME for helping me out BTW! I've really been stressing out about this