SOLVED

Conditional format

New Contributor

 

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 DateaccountClient First NameClient Last NameService
5/3/2021 12:30 PM28458AaronBanksTELD3
5/3/2021 2:00 PM28458AaronBanksTELD7
5/6/2021 12:00 PM105065AdielJonsTELD7
5/14/2021 3:15 PM105065AdielJonsTELD7
5/3/2021 1:45 PM102937AdriennewhiteTELD7
5/24/2021 11:45 AM102937AdriennewhiteTELD7
5/6/2021 2:00 PM105024AleskiBrownTELD7
5/10/2021 11:00 AM105024AleskiBrownTELD3
5/11/2021 11:00 PM105024AleskiBrownTELD3
5/13/2021 1:45 PM105024AleskiBrownTELD7
5/14/2021 8:00 AM105024AleskiBrownD3  - CD Group Session
4 Replies

@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:))

best response confirmed by JulMir0689 (New Contributor)
Solution

i 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 

@JulMir0689 

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)

@Ilgar_Zarbaliyev

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 DateaccountPatient First NamePatient Last NameServiceDurationD15D11(45)D11(30)CDPsmoking changeinjecD22D21D20D19 D 1D14D1515 min screeningD6D16C15D18D8D2D7D3D1045 mins60 mins75 mins90 mins105 mins120 mins120+ mins2nd visit
10/1/2021 2:00 PM28458philSmithTELD745                   1           
10/7/2021 11:45 AM28458philSmithTELD745                  1            
10/29/2021 12:00 PM28458philSmithTELD745                 1             
10/21/2021 9:00 AM107862mikeLoraTELD175       1                       
10/22/2021 10:30 AM107862mikeLoraTELD745                 1             
10/25/2021 9:00 AM107862mikeLoraTELD22=  Telehealth E&M 10-19 min10   1                           
10/25/2021 10:00 AM107862mikeLoraTELD645           1                   
10/27/2021 9:00 AM107862mikeLoraTELD745                 1             
10/29/2021 11:15 AM107862mikeLoraD7  - Individual therapy - Norm 4545                 1      1  1   
10/29/2021 1:15 PM107862mikeLoraD22- Evaluation and Management 10-1910   1                           
10/29/2021 2:00 PM107862mikeLoraD3  - CD Group Session60                  1            
10/1/2021 3:00 PM102937joeReedTELD745                 1             
10/25/2021 2:00 PM102937joeReedTELD745                 1             
10/13/2021 4:00 PM29362billRamosTELD745                 1    1    1   
10/13/2021 6:00 PM29362billRamosTELD1930      1                        
10/20/2021 10:00 AM29362billRamosD19 - CD Peer Support30       1               1    1