Formatting if cell value found in range

MeaclH

Board Regular
Joined
Apr 2, 2014
Messages
109
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Gday all,

I am looking for a formula that will apply conditonal formatting to a cell if the value in that cell is found elsewhere in a range.
In the sample data below for example, in rows 35,38,41,44 and so on, I would like to see if there are duplicates of any values. The one privso I can't seem to figure out is that I only want it to show duplicates for certain values.
This is a roster so there may be multiple people rostered OFF which I don't want to change the formatting of, only the shifts. I'm happy to use the codes of all 20 odd shifts in the formula if that would make it easier?

Hope that makes sense.
Cheers,
Hayden

DIGITAL ROSTER - VERSION 3.0.xlsm
N
35OFF
36
37
380530NCC
39
40
412130PL/S
42
43
441330NCC
45
46
47OFF
48
49
500530PB
51
52
53OFF
54
55
562130PB
57
58
59OFF
60
61
621330PB
63
64
65OFF
66
67
680530PL/S
69
70
71OFF
72
73
74OFF
75
76
771330BC
78
79
802130NCC
81
82
831330PL/S
84
85
86OFF
87
88
892130BC
90
91
920530BC
93
94
FN 23MAR-05APR25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "SPDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CPDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "BONUS"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "MATLVE"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "PATLVE"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "GAZETTE"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "EDO-U"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "OFF-U"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "BLV"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CTFN"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "STFN"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "SDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "LSL"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "A/L"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "PHC"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "OFF"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "EDO"textYES
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COLUMN()=CELL("COL")textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=G$3=TODAY()textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "OK"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "DEC"textNO
 
Hi everyone,

Looking at Kevin's formula, I thought about a little something that could take into account multiple unwanted values (like OFF), and I would apply the conditionnal formating to the whole column C:C with this formula:

- English version -
Excel Formula:
=AND(SUM(--MAP({"OFF", "SOMETHING ELSE", "Another unwanted value"},LAMBDA(unwanted,$C1=unwanted)))=0,COUNTIF($C:$C,$C1)>1)

-French version-
Excel Formula:
=ET(SOMME(--MAP({"OFF";"SOMETHING ELSE";"Another unwanted value"};LAMBDA(unwanted;$C1=unwanted)))=0;NB.SI($C:$C;$C1)>1)
To simplify this, the MAP function will forward one by one the values to the LAMBDA as unwanted argument that will return if it is equal or not. The -- will change True and False to 1 and 0, and if the SUM of all this is 0 (C1 is different from everything in the list) and the value is found more than once in the C:C column then it will color the cell.

Bests regards,

Vincent
 
Last edited:
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Could you list them on a table on the same sheet? I think the CF formula could be made a lot simpler if you could.
Hi Kevin,
Thanks for your ongoing help. Table below of values to be omitted from formula.

Cheers,
Hayden
OFF
OFF-U
EDO
X0930
TRAINING
ADHOC
FRCE
SGS
MTP
AV
SDO
STFN
SPDO
CDO
CTFN
CPDO
PHC
A/L
LSL
BVL
GAZETTE
PATLVE
MATLVE
BONUS
 
Upvote 0
Mate!
This is such a classy touch to offer these suggestions! I had a feeling there was probably a more efficient way of setting the CF up but figuring it out fell by the way side.

Thank you very much for offering up your expertise and taking the time to detail it so well.

I have implemented the changes and everything is working perfectly. Makes for a much tidier sheet and mini sheet when I need to share it.

Love ya work!
Hayden
 
Upvote 0
One way is to have a list (somewhere) of all the exceptions to the CF rule, then reference it in your CF formula. Something like the following:

CF.xlsm
CDE
25OFFExceptions
260530NCCOFF
27OFF-U
280530NCCEDO
29ADHOCX0930
30TRAINING
312130PL/SADHOC
322130PL/SFRCE
33SGSSGS
341330NCCMTP
351330NCCAV
36SDOSDO
37OFFSTFN
38SDOSPDO
39CDOCDO
400530PBCTFN
41SDOCPDO
42CDOPHC
43OFFA/L
44SDOLSL
45BVL
462130PBGAZETTE
472130PBPATLVE
48AVMATLVE
49OFFBONUS
50
51AV
521330PB
53
54AV
55OFF
56
57
580530PL/S
590530PL/S
60
61OFF
622130PB
632130PB
64OFF
65
66
671330BC
68
69
702130NCC
71
720530PL/S
731330PL/S
74
75
76OFF
77
78
792130BC
80
81
820530BC
83ADHOC
84FRCE
85SGS
86MTP
87ADHOC
88FRCE
89SGS
90MTP
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C25:C92Expression=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF($C$25:$C$92,C25)>1)textYES
 
Upvote 0
Hi MeaclH,

Here's what I would do with that list:

Excel Formula:
=AND(SUM(--MAP({"OFF","OFF-U","EDO","X0930","TRAINING","ADHOC","FRCE","SGS","MTP","AV","SDO","STFN","SPDO","CDO","CTFN","CPDO","PHC","A/L","LSL","BVL","GAZETTE","PATLVE","MATLVE","BONUS"},LAMBDA(unwanted,$C1=unwanted)))=0,COUNTIF($C:$C,$C1)>1)

Bests regards,

Vincent
 
Upvote 0
Works a treat mate! Thank you for taking the time.
One thing. I will have 14 columns for a fortnight; how to have it apply column by column? For reference, actual project mini sheet attached.
DIGITAL ROSTER - VERSION 3.0.xlsm
GHIJKLMNOPQRST
350530PB1330BC1330PB1330CFD1330PL/SEDO-UOFF-UOFF-U1330BC1330PB1330CFDOFF1330NRN1330NCC
361745NCC DEC
37
38OFF-UOFF-U0530CFD0530PL/SOFF-UOFF-U0530PB0530NCCOFF-U0530PB0530PL/S0530CFD0530PL/SOFF
39DEC 0130 EXT
40FWADEC 1745 EXTFWA
41OFF-UOFF-U2130NCC2130BC2130PB2130PB2130NCC2130PL/S2130BC2130PBEDOOFF2130PL/SOFF
42
43
441330BCOFF1330CFD1330PL/S1330NRN1330BC1330PB1330NCCOFFOFFOFF1330NRN1330BC1330PB
45EXT 0145 PL/S OK
46
472130PL/S2130BC2130PL/SOFF-UOFF-U2130NCC2130PL/SOFF-U2130PL/S2130BC2130PB2130NCCEDO-UOFF-U
48EXT 1730NCC OK
49
500600RGT0530NRN0530NRNOFF-UOFF0530NRN0530BC0530PB0530CFD0530PL/S0530NRN0530PBOFFOFF
51
52AT METROL
531400RGT1330PL/S1330NRN1330BCEDO-UOFF-UOFF-UOFF-UOFF-U1330NRN1330BCBONUSPHCPHC
54
55AT METROL
562130PB2130NCCOFF-UOFF-U2130NCC2130PL/S2130BC2130PB2130NCCOFF-UOFF-U2130BC2130PBPHC
57
58
59OFF-UOFF-UX0930SDO0530PB0530CFD2130PBOFF-U0530NRN0530BC0530PBOFF-U0530CFD0530PL/S
60
61
621330PL/S1330NRN1330BCOFFOFF1330NRN1330BC1330PB1330CFD1330PL/S1330NRN1330PL/SOFFOFF
63EXT 0930PL/S OK
64
65STFNSTFNSTFNSTFNEDOOFFOFFOFFOFF2130PL/S2130BC2130PB2130NCC2130PL/S
66
67
68OFFOFFSDO0530PB0530CFD0530PL/S0530NCC0530PL/S0530BCOFFOFF0530BC0530PB0530NCC
69DEC 0130 EXT
70DEC 1745 EXT
710530BC0530PBOFFEDO0530NRN0530BCOFFOFFAV0530NRNOFF0530NRN0530BC0530PB
72EXT 1745NCC OKEXT 0130PB OK
73
74OFF1330PB2130BCEDO-U1330CFD1330PL/SOFFOFF1330PL/S1330BC1330PB1330CFD1330PL/S1330BC
75
76
771330PB1330CFDOFF-UOFF-U1330PB1330CFD1330PL/S1330BC1330PB1330CFD1330PL/SEDOOFFOFF
78
79
802130NCC2130PL/S2130PB2130NCC2130PL/S2130BCOFF2130NCCOFFOFF2130NCC2130PL/S2130BC2130PB
81EXT 1730PB OK
82
83OFF-UOFF1330PL/S1330NRN1330BC1330PB1330NCC1330PL/S1330NRNEDOOFF1330BC1330PBOFF
84
85
86A/L0530CFD0530PL/S0530NRN0530BC0530PBOFFOFFOFFOFF0530CFD0530PL/S0530NRN0530BC
87EXT 1745PB OK
88
892130BC2130PBOFF2130PL/S2130BCOFFOFF2130BC2130PB2130NCC2130PL/SEDOOFF2130BC
90
91
920530NCC0530BC0530PB0530CFD0530PL/SOFF-U0530PL/S0530BC0530PB0530CFD0530BCOFF-UOFF-UOFF-U
93DEC 0130 EXT
94
FN 26JAN-08FEB25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=AND(COUNTIF($AU$18:$AU$41,G19)=0,COUNTIF($G$19:$G$110,G19)>1)textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*EDO*"), COUNTIF(G19,"*EDO-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*OFF*"), COUNTIF(G19,"*OFF-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textYES
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=G$3=TODAY()textNO
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COUNTIF(DATA!$AV$4:$AV$16,G$3)=1textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COLUMN()=CELL("COL")textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "OK"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "DEC"textNO
Cells with Data Validation
CellAllowCriteria
G35:T35List=DATA!$AF$4:$AF$55
G44:T44List=DATA!$AF$4:$AF$55
G47:T47List=DATA!$AF$4:$AF$55
G50:T50List=DATA!$AF$4:$AF$55
G53:T53List=DATA!$AF$4:$AF$55
G56:T56List=DATA!$AF$4:$AF$55
G59:T59List=DATA!$AF$4:$AF$55
G62:T62List=DATA!$AF$4:$AF$55
G65:T65List=DATA!$AF$4:$AF$55
G68:T68List=DATA!$AF$4:$AF$55
G71:T71List=DATA!$AF$4:$AF$55
G74:T74List=DATA!$AF$4:$AF$55
G77:T77List=DATA!$AF$4:$AF$55
G80:T80List=DATA!$AF$4:$AF$55
G83:T83List=DATA!$AF$4:$AF$55
G86:T86List=DATA!$AF$4:$AF$55
G89:T89List=DATA!$AF$4:$AF$55
G92:T92List=DATA!$AF$4:$AF$55
G38:T38List=DATA!$AF$4:$AF$55
G41:T41List=DATA!$AF$4:$AF$55
 
Upvote 0
Maybe it's an issue with the cells I have applied it to and the absolute references. I only want it to apply to cells in row 19,22,25,28..etc in each column.
 
Upvote 0
Hi Vincent,

Thanks for your contributions! I'm a bit lost, is this a CF formula? I am getting a response "you may not use LAMBA functions for CF"

Cheers,
Hayden
 
Upvote 0
To apply it easily to multiple columns, leave the absolute reference in the first part of the formula alone, but take the absolute references off the second part, like this:

Rich (BB code):
=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF(C25:C92,C25)>1)

You can change the addresses to your actual locations first of course. Then highlight the first column of interest & copy the formats across to your entire range.
 
Upvote 0
Hi Vincent,

Thanks for your contributions! I'm a bit lost, is this a CF formula? I am getting a response "you may not use LAMBA functions for CF"

Cheers,
Hayden
Hi Hayden,

You're right by adding a lambda you end up with an error.

cheers,

Vincent
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,059
Members
453,592
Latest member
bcexcel

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