Formula with conditions of 7 days

F

faisus

Board Regular
Joined
Jul 8, 2012
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Hello everyone, I hope everything is well with you.

I am stuck in a bit tricky situation and request your help with a formula. I have a list as below and i would like to know if a member with card number in Column A was billed with same provider & with same clinician within 7days of the 1st visit.
For instance the highlighted member in yellow was billed within 7 days by clinician Kenja. I require an easy way to know how can i find out without going one by one.

Thank you all


Follow up.xlsx
ABCDEFG
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAMETREATMENT DATETotal
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA14/03/2023130.50
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR18/04/2023140.00
4101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA06/05/2023130.50
5101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan21/05/2023211.25
6101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED23/05/2023549.50
7101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO13/06/2023104.00
8101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO20/06/2023104.00
9101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO28/07/2023211.25
10101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY03/08/2023140.00
Sheet1
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
faisus said:
I require an easy way to know how can i find out without going one by one.
Click to expand...
Two things required -

What version of excel you are using right now? Update the same in your profile too...
Though your sample data shows sorted data but I believe your data in routine is not as sorted as your sample data. Please confirm?
 
Upvote 0
SanjayGMusafir said:
Two things required -

What version of excel you are using right now? Update the same in your profile too...
Though your sample data shows sorted data but I believe your data in routine is not as sorted as your sample data. Please confirm?
Click to expand...
HI i am using
1699440981635.png
. Yes the data is not sorted.
 
Upvote 0
faisus said:
i would like to know if a member with card number in Column A was billed with same provider & with same clinician within 7days of the 1st visit.
Click to expand...
Is this what you are trying to achieve. Check and revert -

Book1
ABCDEFGH
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalCheck
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5 
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140 
4101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5 
5101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25 
6101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5 
7101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104 
8101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104Yes
9101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25 
10101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140 
Sheet1
Cell Formulas
RangeFormula
H2:H10H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:H10Expression=$H1="Yes"textNO
 
Upvote 0
I just realized if there are more than two visits in the log then we need to have a better way of doing things. Check Column I. I think it should serve your purpose better, which Option 1 won't do. Check and revert

Book1
ABCDEFGHI
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalOption 1Better
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5  
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140  
4101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO5/1/23
5101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5  
6101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25  
7101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5  
8101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104  
9101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104 Yes
10101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25  
11101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140  
Sheet1
Cell Formulas
RangeFormula
H2:H3,H5:H11H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
I2:I3,I5:I11I2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$E$1:E1,E2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:F11Expression=$I11="Yes"textNO
A1:I10Expression=$I1="Yes"textNO
 
Upvote 0
SanjayGMusafir said:
I just realized if there are more than two visits in the log then we need to have a better way of doing things. Check Column I. I think it should serve your purpose better, which Option 1 won't do. Check and revert

Book1
ABCDEFGHI
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalOption 1Better
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5  
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140  
4101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO5/1/23
5101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5  
6101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25  
7101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5  
8101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104  
9101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104 Yes
10101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25  
11101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140  
Sheet1
Cell Formulas
RangeFormula
H2:H3,H5:H11H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
I2:I3,I5:I11I2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$E$1:E1,E2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:F11Expression=$I11="Yes"textNO
A1:I10Expression=$I1="Yes"textNO
Click to expand...

Thank you for your help. Will check and confirm.
 
Upvote 0
SanjayGMusafir said:
I just realized if there are more than two visits in the log then we need to have a better way of doing things. Check Column I. I think it should serve your purpose better, which Option 1 won't do. Check and revert

Book1
ABCDEFGHI
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalOption 1Better
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5  
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140  
4101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO5/1/23
5101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5  
6101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25  
7101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5  
8101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104  
9101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104 Yes
10101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25  
11101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140  
Sheet1
Cell Formulas
RangeFormula
H2:H3,H5:H11H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
I2:I3,I5:I11I2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$E$1:E1,E2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:F11Expression=$I11="Yes"textNO
A1:I10Expression=$I1="Yes"textNO
Click to expand...

Hi yes it works perfectly for Clinician name. However when i try to do for Clinician Speciality by shifting the formula from name, it does not give me correct result.

Could you please check and advise if i would like to do same by replacing clinican name with Clinican speciality.

Thanks alot for your help.
 
Upvote 0
faisus said:
Could you please check and advise if i would like to do same by replacing clinican name with Clinican speciality
Click to expand...
Check Below in Column J

Book1
ABCDEFGHIJ
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalOption 1Betterfor Clin Speciality
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5   
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140   
4101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO5/1/23 
5101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5   
6101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25   
7101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5   
8101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104   
9101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104 YesYes
10101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25   
11101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140   
Sheet1
Cell Formulas
RangeFormula
H2:H3,H5:H11H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
I2:I3,I5:I11I2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$E$1:E1,E2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
J2:J11J2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$C$1:C1,C2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
 
Upvote 0
SanjayGMusafir said:
Check Below in Column J

Book1
ABCDEFGHIJ
1MEDICAL CARD NOCLAIM NOCLIN SPECIALITYPROVIDER CODECLINICIAN NAME TREATMENT DATE TotalOption 1Betterfor Clin Speciality
2101-102-0001150401-01C/2023/208841OphthalmologistM722RINI SAHA3/14/23130.5   
3101-102-0001150401-01C/2023/318953Internal MedicineH002ARUN MOHAN MOHANAKUNARAN NAIR4/18/23140   
4101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO5/1/23 
5101-102-0001150401-01C/2023/395561OphthalmologistM722RINI SAHA5/6/23130.5   
6101-102-0001150401-01C/2023/487259OphthalmologistH050Mona Marwan omar Marwan5/21/23211.25   
7101-102-0001150401-01C/2023/639434Internal MedicineH050AHMED FOUAD AHMED SAYED AHMED5/23/23549.5   
8101-102-0001150401-01C/2023/601987OphthalmologistH050KENJA MADHAVA RAO6/13/23104   
9101-102-0001150401-01C/2023/616389OphthalmologistH050KENJA MADHAVA RAO6/20/23104 YesYes
10101-102-0001150401-01C/2023/749067OphthalmologistH050KENJA MADHAVA RAO7/28/23211.25   
11101-102-0001150401-01C/2023/771838Internal MedicineH002MANOHAR VENKATA REDDY8/3/23140   
Sheet1
Cell Formulas
RangeFormula
H2:H3,H5:H11H2=LET(tDt,XLOOKUP(1,($A$1:A1=A2)*($D$1:D1=D2)*($E$1:E1=E2),$F$1:F1,0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
I2:I3,I5:I11I2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$E$1:E1,E2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
J2:J11J2=LET(tDt,IFERROR(MAXIFS($F$1:F1,$A$1:A1,A2,$D$1:D1,D2,$C$1:C1,C2),0),IF(TRUNC(F2)-TRUNC(tDt)<=7,"Yes",""))
Click to expand...
Thank you so much, it works perfectly. I was trying with H and was not taking clinic specilaity.

Thank you again.
 
Upvote 0
Glad to help you and thanks for the feedback.

It is Recommended to mark the post that worked best for you as solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
820
juanam
J
B
  • Question Question
Index/Match Formulas for Dates and business days
Replies
0
Views
583
banneduser123
B
M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top