count the number of visits of a client

[renemwizere]

I work in medical insurance
And I tried to use this formula to count the number of visits of a client
=IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

But in some cases the formula counts the same date as two visits instead of one

Please advice!

 

[mole999]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

in the UK you should normally be reporting repeat visits, it indicates that treatment may not have been effective

 

[renemwizere]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

A client may attend different departments in a hospital the same day, that counts like one visit!

 

[S.H.A.D.O.]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

If you could explain what is in each of the B2=B1,E2=E1,F2-F1 cells then we will be able to help you.

It sounds as if the attendance date is the main criteria considering they could attend several different departments on the same day.

Uploading a worksheet would also be helpful, with sensitive data replaced obviously.

I hope this helps!

 

[renemwizere]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Here is the image I cut from Excel
B stands for Member Number
E stands for Provider Name
And F stands for Date
Each will get a number, say B2, respectively from the row where it is in the excel sheet

[/IMG]

 

[renemwizere]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Fair point S.H.A.D.O

 

[S.H.A.D.O.]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Give this a go...

=IF(AND(B2=B1,F2=F1),"Unique Visit","")

It's not important how many departments they visited, is it?

I hope this helps!

 

[renemwizere]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Thank for your time
I appreciate alot

But the code you proposed does not include the provider name
Which is very key to identify a visit

 

[Aladin Akyurek]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Try to post the sample in an Excel readable form. For providing a sample try: Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

 

[S.H.A.D.O.]

Re: =IFERROR(IF(AND(B2=B1,E2=E1,F2-F1<1)," ","Uniq Visit"),"Uniq Visit")

Is this any closer...

=IF(AND(B2<>B1,F2=F1,E2<>E1),CONCATENATE(B1,", ",E1,", ",TEXT((F1),"DD/MM/YYYY")," - Unique Visit"),"")

I hope this helps!