Oct 05 2021 05:03 PM
Having the hardest times setting up a conditional format were i need to highlight a duplicated date with the client first name. for instance Aaron had two services the same day 5/3/21 but when i do a conditional format Adrienne is highlighted as well but she does not have a duplicate service for the same day. i need to find a way to highlight clients that receive multiple services on the same day.
Appt Date | account | Client First Name | Client Last Name | Service |
5/3/2021 12:30 PM | 28458 | Aaron | Banks | TELD3 |
5/3/2021 2:00 PM | 28458 | Aaron | Banks | TELD7 |
5/6/2021 12:00 PM | 105065 | Adiel | Jons | TELD7 |
5/14/2021 3:15 PM | 105065 | Adiel | Jons | TELD7 |
5/3/2021 1:45 PM | 102937 | Adrienne | white | TELD7 |
5/24/2021 11:45 AM | 102937 | Adrienne | white | TELD7 |
5/6/2021 2:00 PM | 105024 | Aleski | Brown | TELD7 |
5/10/2021 11:00 AM | 105024 | Aleski | Brown | TELD3 |
5/11/2021 11:00 PM | 105024 | Aleski | Brown | TELD3 |
5/13/2021 1:45 PM | 105024 | Aleski | Brown | TELD7 |
5/14/2021 8:00 AM | 105024 | Aleski | Brown | D3 - CD Group Session |
Oct 05 2021 07:21 PM
@JulMir0689 Hi there,
I made slight changes in your excel file, solved the issue and attached to this email.
I changed date to day format. Added two repetitive rows to be sure that the conditional formatting works well.
To solve this issue, I used Countifs and If functions.
Have a good day.
P.S.
=IF(COUNTIFS($A$2:$A$14,A2,$C$2:$C$14,C2)>1,TRUE, FALSE)
If you like this solution, please highlight:))
Oct 05 2021 07:35 PM
Solutioni like it but it would be helpful if it would be possible to have a number rather than true or false for instance if there are two services for the one day have a number 2 and if three services in the same day a number 3 is that possible?@Ilgar_Zarbaliyev
Oct 05 2021 09:38 PM
Conditional Formatting is working on basis of Boolean Logic (True or False Values).
Actually, if you take if function from my formula, it will give you numbers.
=COUNTIFS($A$2:$A$14,A2,$C$2:$C$14,C2)
Nov 12 2021 11:52 AM
I continue to have the hardest time figuring this out, can you help me again?
I need to find a way to conditional format the same person with multiple dates that are the same (cell 10,11 and 12) then count the duration (Column F 120 minutes) and place a #1 in AH and since its more than one service cell AK needs a 1 in it Aswell as a 1 in cells M because its a D22 AA because its a D7 and AB because its a D3
but this is definitely to complicated for me, i am not even sure if it is possible.
do you think you can help me?
Appt Date | account | Patient First Name | Patient Last Name | Service | Duration | D15 | D11(45) | D11(30) | CDP | smoking change | injec | D22 | D21 | D20 | D19 | D 1 | D14 | D15 | 15 min screening | D6 | D16 | C15 | D18 | D8 | D2 | D7 | D3 | D10 | 45 mins | 60 mins | 75 mins | 90 mins | 105 mins | 120 mins | 120+ mins | 2nd visit |
10/1/2021 2:00 PM | 28458 | phil | Smith | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/7/2021 11:45 AM | 28458 | phil | Smith | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/29/2021 12:00 PM | 28458 | phil | Smith | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/21/2021 9:00 AM | 107862 | mike | Lora | TELD1 | 75 | 1 | ||||||||||||||||||||||||||||||
10/22/2021 10:30 AM | 107862 | mike | Lora | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/25/2021 9:00 AM | 107862 | mike | Lora | TELD22= Telehealth E&M 10-19 min | 10 | 1 | ||||||||||||||||||||||||||||||
10/25/2021 10:00 AM | 107862 | mike | Lora | TELD6 | 45 | 1 | ||||||||||||||||||||||||||||||
10/27/2021 9:00 AM | 107862 | mike | Lora | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/29/2021 11:15 AM | 107862 | mike | Lora | D7 - Individual therapy - Norm 45 | 45 | 1 | 1 | 1 | ||||||||||||||||||||||||||||
10/29/2021 1:15 PM | 107862 | mike | Lora | D22- Evaluation and Management 10-19 | 10 | 1 | ||||||||||||||||||||||||||||||
10/29/2021 2:00 PM | 107862 | mike | Lora | D3 - CD Group Session | 60 | 1 | ||||||||||||||||||||||||||||||
10/1/2021 3:00 PM | 102937 | joe | Reed | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/25/2021 2:00 PM | 102937 | joe | Reed | TELD7 | 45 | 1 | ||||||||||||||||||||||||||||||
10/13/2021 4:00 PM | 29362 | bill | Ramos | TELD7 | 45 | 1 | 1 | 1 | ||||||||||||||||||||||||||||
10/13/2021 6:00 PM | 29362 | bill | Ramos | TELD19 | 30 | 1 | ||||||||||||||||||||||||||||||
10/20/2021 10:00 AM | 29362 | bill | Ramos | D19 - CD Peer Support | 30 | 1 | 1 | 1 |
Oct 05 2021 07:35 PM
Solutioni like it but it would be helpful if it would be possible to have a number rather than true or false for instance if there are two services for the one day have a number 2 and if three services in the same day a number 3 is that possible?@Ilgar_Zarbaliyev