Unique Count Problem

I have many rows (over 100,000). I want to figure out a way to get the unique counts of rows given certain parameters.

In my data set I have rows: Day, Month, Year, Physician ID, Patient ID, Encounter ID, Procedure Name

There are multiple procedures per Encounter. Patient IDs occur many times a month.

I want to know how many unique Patient IDs exist per month, per a certain physician.

A1 in a different sheet contains the Physician ID
A2 contains Procedure Name

A3:A15 are Month Names.
I want B3:B15 to populate.

An example of a formula I use sometimes {=SUM(IF(FREQUENCY(IF('EP Data'!I:I>=A3,IF('EP Data'!I:I<=B3,IF('EP Data'!D:D=$A$1,IF(LEFT('EP Data'!E:E,2)="OP",'EP Data'!I:I)))),'EP Data'!I:I),1))} to count unique instances of multiple parameters around a variable. This formula is extremely slow and does not work on a huge dataset.

Any good way of approaching this general problem would be greatly appreciated.

Thank you,

Please see attached file for help.

I would be cycling through all the providers based on a drop list that I would have in G1

I've done that before where I would sort by patient and then use floating ranges of 1k or something to cut down on iterations. This severely limits my querrying ability when I try doing analysis around other variables on the same data (eg Doc ID).

Thanks,

I have 64-Bit 2013. Is there a version that will work with it?

I am not seeing any VBA in the attached file. What do you mean?

Hi vichisov,

I'm using excel 2007 on Windows 8 but I believe the the workbook will work for you. The macros used that I was referencing by vba is in module one

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
total = total + rng.Value
End If
Next
SumVisible = total


End Function

as well as some in the sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.Sheets("Sheet1").Calculate


End Sub

Anyway, the example was meant as an option. There are probably better ways to accomplish what you want but this is the best I could do. Good luck!

If the function (i.e. macro used above) doesn't work in a 64-bit version, there might be another way to auto sum the filtered "1's" used to individualize occurrences, in each field you want summarized. As the "1's" are generated off a formula, I had difficulty finding a way to sum them when filtered without the use of the macro above. I'm sure someone might be able to assist you who has more knowledge than I in such formulas. The plus side of finding an alternate method is that you could then hide those columns which contain the "1's", which I could not currently do with this macro. Also of note, I replaced the sample workbook above and replaced it with a newer version. I found that the patient id's when filtered would sometimes render "patient ID" in the summary page and not the actual #. This now has been fixed. If you find a formula to fix the summation of the "1's", I'd be interested to know what you use. Best of luck to you!

lilsnoop,

Why not use a countifs? If something else is generating the flags (1's) it should work if I am understanding what you mean by filtered 1s.

Honestly this just might be above Excel's pay grade and in reality a SQL problem. I did find http://colinlegg.wordpress.com/2013/...alues-vba-udf/ (apologies if I am violating board rules by posting a link). I got one of the codes to work on a large range, but could not figure out how to incorporate certain filters for other parameters such as Doc ID/Procedure ID/Date Range.

Thoughts?

=subtotal(102,Range)

This would count the number of unhidden elements in the range.

Out of curiosity
Is this any faster?

Sub Maybe()
    Dim j As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False
    For j = 1 To 12
        With Sheet1.Cells(1).CurrentRegion
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=[H1]

            With Sheet1.Cells(1).CurrentRegion.SpecialCells(12)
                .AutoFilter Field:=2, Criteria1:=[H2]

                With Sheet1.Cells(1).CurrentRegion.SpecialCells(12)
                    .AutoFilter Field:=4, Criteria1:=j

                End With
            End With
        End With
        On Error Resume Next
        Range("H" & j + 3).Value = Sheets("Sheet1").Range("A3:A" & lr).SpecialCells(12).Count
        On Error GoTo 0
    Next j
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Ok. Solved your monthly break down issue as well. This workbook contains no macros only formulas. I've explained any shortcomings in the summary tab of this attached workbook. Otherwise I hope it works for you.

Ok. I believe this will do exactly what you want. A lot of formulas though.

lilsnoop,

Thank you for all the time you spent on this. I think ultimately this still wont work. I have 30 or so procedures and another 80 physicians. Manually working through this for various time frames will not work. I am very impressed however by how you manage to count unique cells so quickly.

I spent much of the day reading up on powerpivot expansion. Seems like it is the way to go. I will give it a try when I get home (can't do it on the work machine). Will see if I can replicate what adyteo suggested. Perhaps the longhand of creating something that will be truly input driven is your solution in conjunction with jolivanes' (still trying to understand what is going on in the code and how to use it) autofiltration.

No problem. Best of luck to you!

Check out bsalv's solution. I think he got it.

bsalv,

Seeing as I am not nearly as proficient as you, what is the general principle that you use to speed this up so dramatically?

Sorry for the slow response. I was away from a computer for some time.

I tried finding where I said something about 5 million entries and could not find it. In any case, I should have said ~500k. Everything fits in one table.

Right now I am working on learning what you did and then trying to replicate it for a possibly more complicated application as the example we used does not have much in terms of if parameters.