SOLVED

Conditional format

%3CLINGO-SUB%20id%3D%22lingo-sub-2815353%22%20slang%3D%22en-US%22%3EConditional%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2815353%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20the%20hardest%20times%20setting%20up%20a%20conditional%20format%20were%20i%20need%20to%20highlight%20a%20duplicated%20date%20with%20the%20client%20first%20name.%20for%20instance%20Aaron%20had%20two%20services%20the%20same%20day%205%2F3%2F21%20but%20when%20i%20do%20a%20conditional%20format%26nbsp%3BAdrienne%20is%20highlighted%20as%20well%20but%20she%20does%20not%20have%20a%20duplicate%20service%20for%20the%20same%20day.%20i%20need%20to%20find%20a%20way%20to%20highlight%20clients%20that%20receive%20multiple%20services%20on%20the%20same%20day.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22567%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22132%22%3EAppt%20Date%3C%2FTD%3E%3CTD%20width%3D%2254%22%3Eaccount%3C%2FTD%3E%3CTD%20width%3D%22118%22%3EClient%20First%20Name%3C%2FTD%3E%3CTD%20width%3D%22116%22%3EClient%20Last%20Name%3C%2FTD%3E%3CTD%20width%3D%22147%22%3EService%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F3%2F2021%2012%3A30%20PM%3C%2FTD%3E%3CTD%3E28458%3C%2FTD%3E%3CTD%3EAaron%3C%2FTD%3E%3CTD%3EBanks%3C%2FTD%3E%3CTD%3ETELD3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F3%2F2021%202%3A00%20PM%3C%2FTD%3E%3CTD%3E28458%3C%2FTD%3E%3CTD%3EAaron%3C%2FTD%3E%3CTD%3EBanks%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F6%2F2021%2012%3A00%20PM%3C%2FTD%3E%3CTD%3E105065%3C%2FTD%3E%3CTD%3EAdiel%3C%2FTD%3E%3CTD%3EJons%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F14%2F2021%203%3A15%20PM%3C%2FTD%3E%3CTD%3E105065%3C%2FTD%3E%3CTD%3EAdiel%3C%2FTD%3E%3CTD%3EJons%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F3%2F2021%201%3A45%20PM%3C%2FTD%3E%3CTD%3E102937%3C%2FTD%3E%3CTD%3EAdrienne%3C%2FTD%3E%3CTD%3Ewhite%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F24%2F2021%2011%3A45%20AM%3C%2FTD%3E%3CTD%3E102937%3C%2FTD%3E%3CTD%3EAdrienne%3C%2FTD%3E%3CTD%3Ewhite%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F6%2F2021%202%3A00%20PM%3C%2FTD%3E%3CTD%3E105024%3C%2FTD%3E%3CTD%3EAleski%3C%2FTD%3E%3CTD%3EBrown%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F10%2F2021%2011%3A00%20AM%3C%2FTD%3E%3CTD%3E105024%3C%2FTD%3E%3CTD%3EAleski%3C%2FTD%3E%3CTD%3EBrown%3C%2FTD%3E%3CTD%3ETELD3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F11%2F2021%2011%3A00%20PM%3C%2FTD%3E%3CTD%3E105024%3C%2FTD%3E%3CTD%3EAleski%3C%2FTD%3E%3CTD%3EBrown%3C%2FTD%3E%3CTD%3ETELD3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F13%2F2021%201%3A45%20PM%3C%2FTD%3E%3CTD%3E105024%3C%2FTD%3E%3CTD%3EAleski%3C%2FTD%3E%3CTD%3EBrown%3C%2FTD%3E%3CTD%3ETELD7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F14%2F2021%208%3A00%20AM%3C%2FTD%3E%3CTD%3E105024%3C%2FTD%3E%3CTD%3EAleski%3C%2FTD%3E%3CTD%3EBrown%3C%2FTD%3E%3CTD%3ED3%26nbsp%3B%20-%20CD%20Group%20Session%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2815353%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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
3 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)