Using Conditional Formatting to Highlight Positions

[Newbienew]

Hello

I am attempting to do a complicated "for me" conditional format. I want to be able to highlight any ratings on a separate spreadsheets column (E) based on the column A and I of the rating and column B and J if its greater than 0. I am not about to move these columns. The issue is column E on the separate sheet has different variables at the end of the ratings, ranging from 1,2,3,sn,an,cn,fn and c, cs. The formula would have to be able to turn any rating green on the E column of the other sheet regardless of the variable endings. I have posted the A and B columns first ant the E column of the other sheet as well. Please help Thank you.

Copy of REM SLOW WORKBOOK AUG5.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Rate	Available	BNA		ASchool				Rate	Available
2	A	0	0		0				ABE	0
3	ABF	5	0		0				ABH	0
4	AC	0	0		0				AD	0
5	ADEK	0	0		0				ADSP	0
6	AE	0	0		0				AECF	0
7	AG	0	0		0				AIRC	0
8	AIRR	0	0		0				AM	0
9	AME	0	0		0				AMEK	0
10	AN	0	0		0				AO	0
11	AS	0	0		0				AT	0
12	AV	0	0		0				AW	0
13	AWF	0	0		0				AWO	0
14	AWR	0	0		0				AWS	0
15	AZ	30	0		0				BM	0
16	BU	0	0		0				CE	0
17	CM	0	0		0				CS	0
18	CSS	0	0		0				CTI	0
19	CTM	0	0		0				CTN	0
20	CTR	456	0		0				CTT	0
21	DC	0	0		0				E	0
22	EA	0	0		0				EM	0
23	EMN	0	0		0				EN	0
24	EO	0	0		0				EOD	0
25	ET	0	0		0				ETN	0
26	ETR	0	0		0				ETV	0
27	FC	0	0		0				FCA	0
28	FN	0	0		0				FT	0
29	GM	45	0		0				GSE	0
30	GSM	0	0		0				HM	48
31	HMBHT	0	0		0				HMDA	0
32	HMDH	0	0		0				HMFM	0
33	HML	0	0		0				HT	0
34	IC	0	0		0				IS	0
35	IT	0	0		0				ITS	0
36	LN	0	0		0				LS	0
37	LSS	0	0		0				MA	0
38	MC	0	0		0				MM	0
39	MMA	0	0		0				MMN	0
40	MMS	0	0		0				MMW	0
41	MN	54	0		0				MR	0
42	MT	0	0		0				MU	0
43	NC	0	0		0				ND	0
44	NUC	0	0		0				OS	0
45	PC	0	0		0				PR	0
46	PS	0	0		0				QM	0
47	RP	0	0		0				RS	0
48	S	0	0		0				SB	0
49	SECF	0	0		0				SENG	0
50	SH	0	0		0				SK	0
51	SKS	0	0		0				SN	0
52	SNL	0	0		0				SO	0
53	STG	0	0		0				STS	0
54	SW	0	0		0				UCT	0
55	UT	0	0		0				YN	0
56	YNS	0	0		0
Sheet2

Copy of REM SLOW WORKBOOK AUG5.xlsb
	E
2	ABE2
3	ABE3
4	ABEAN
5	ABECS
6	ABF1
7	ABF3
8	ABFAN
9	ABFAN
10	ABFC
11	ABFFAN
12	ABH1
13	ABH2
14	ABH3
15	ABHAN
16	ABHC
17	AC2
18	ACCS
19	AD2
20	AD3
21	ADAN
22	AE1
23	AE2
24	AE3
25	AG3
26	AM1
27	AM2
28	AM3
29	AMCS
30	AME1
31	AME2
32	AME3
33	AN
34	AO
35	AO2
36	AO3
37	AOAN
38	AOAN
39	AOCS
40	AS1
41	AS2
42	AS3
43	ASCS
44	AT
45	AT1
46	AT1
47	AT2
48	AT2
49	AT3
50	AWS1
51	AZ1
52	AZ2
53	AZAN
54	BM1
55	BM2
56	BM3
57	BMC
58	BU2
59	CE3
60	CM2
61	CM3
62	CMDCS
63	CS1
64	CS2
65	CS3
66	CSS2
67	CSS3
68	CSSN
69	CTN1
70	CTN2
71	CTN3
72	CTR1
73	CTR2
74	CTT1
75	CTT2
76	CTT3
77	DC2
78	DC3
79	EM2
80	EM3
81	EMC
82	EMN1
83	EMN2
84	EMN3
85	EMNCM
86	EN2
87	EN3
88	EO3
89	ET1
90	ET2
91	ET3
92	ETN1
93	ETN2
94	ETN3
95	ETV2
96	FC1
97	FC2
98	FC3
99	FCA1
100	FCA2
101	FCA3
102	FCAC
103	GM1
104	GM2
105	GM3
106	GSCS
107	GSE1
108	GSE2
109	GSEC
110	GSM2
111	GSM3
112	GSMFN
113	HM1
114	HM2
115	HM3
116	HMCS
117	HN
118	HNSN
119	HT1
120	HT2
121	IC1
122	IC2
123	IC3
124	IS1
125	IS2
126	IT1
127	IT2
128	IT3
129	ITSN
130	LN2
131	LS1
132	LS2
133	LS3
134	LS3
135	LSC
136	LSSC
137	LSSN
138	MA1
139	MA2
140	MA3
141	MAC
142	MASN
143	MC1
144	MC2
145	MC3
146	MM1
147	MM2
148	MM3
149	MM3
REM TEAM JUN

Copy of REM SLOW WORKBOOK AUG5.xlsb
	E
150	MMA2
151	MMFN
152	MMN1
153	MMN2
154	MMN2
155	MMNC
156	MN1
157	MT1
158	MT2
159	MU3
160	NC1
161	NCC
162	OS2
163	OS3
164	OSSN
165	PR1
166	PR2
167	PS
168	PS2
169	PS3
170	QM1
171	QM2
172	RS3
173	SB1
174	SB2
175	SH2
176	SHSN
177	SO1
178	STG1
179	STG2
180	STG3
181	STS1
182	STS2
183	STS3
184	STSCS
185	TBD
186	TM2
187	TM3
188	YN2
189	YN3
190	YNSC
REM TEAM JUN

 

[CA_Punit]

This formula will only result in colour when the Number starts with as prefix so if you have A criteria with Value greater than 1, it will not show,

For Eg A=10, so in BWA it will result in colour match as A was available in 3 position (Considering my earlier formula)

But if you consider

=SUM(IFERROR(IF(SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N7)>1,FALSE,SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N7)),0))>1

It will not show any any match

 

[Fluff]

With sheet 2 as per your OP

+Fluff New.xlsm
	E	F
1
2	ABE2	ABE
3	ABE3	ABE
4	ABEAN	ABE
5	ABECS	ABE
6	ABF1	ABF
7	ABF3	ABF
8	ABFAN	ABF
9	ABFAN	ABFAN
10	ABFC	ABF
11	ABFFAN	ABFF
12	ABH1	ABH
13	ABH2	ABH
14	ABH3	ABH
15	ABHAN	ABH
16	ABHC	ABH
17	AC2	AC
18	ACCS	AC
19	AD2	AD
20	AD3	AD
21	ADAN	AD
22	AE1	AE
23	AE2	AE
24	AE3	AE
25	AG3	AG
26	AM1	AM
27	AM2	AM
28	AM3	AM
29	AMCS	AM
30	AME1	AME
31	AME2	AME
32	AME3	AME
33	AN	AN
34	AO	AO
35	AO2	AO
36	AO3	AO
37	AOAN	AO
38	AOAN	AOAN
39	AOCS	AO
40	AS1	AS
41	AS2	AS
42	AS3	AS
43	ASCS	AS
44	AT	AT
45	AT1	AT
46	AT1	AT1
47	AT2	AT
48	AT2	AT2
49	AT3	AT
50	AWS1	AWS
51	AZ1	AZ
52	AZ2	AZ
53	AZAN	AZ
54	BM1	BM
55	BM2	BM
56	BM3	BM
57	BMC	BM
58	BU2	BU
59	CE3	CE
60	CM2	CM
61	CM3	CM
62	CMDCS	CMD
63	CS1	CS
64	CS2	CS
65	CS3	CS
66	CSS2	CSS
67	CSS3	CSS
68	CSSN	CS
69	CTN1	CTN
70	CTN2	CTN
71	CTN3	CTN
72	CTR1	CTR
73	CTR2	CTR
74	CTT1	CTT
Sheet1

Cell Formulas
Range	Formula
F2:F74	F2	=IF(OR(E2={"sn","an","cn","fn","cs"}),E2,IF(OR(RIGHT(E2)={"1","2","3","c"}),LEFT(E2,LEN(E2)-1),IF(OR(RIGHT(E2,2)={"sn","an","cn","fn","cs"}),LEFT(E2,LEN(E2)-2),E2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E190	Expression	=COUNTIFS(Sheet2!$A$2:$I$57,F2,Sheet2!$B$2:$J$57,">0")	text	NO

 

[Newbienew]

Am I able to share a dropbox link?

 

[Fluff]

Yes, as long as you post the link to the thread.

 

[Newbienew]

Ok so I have attached the spread sheet. Hopefully this will provide some clarity
Project 75

 

[Fluff]

You haven't applied the formula or conditional formatting rule, so I have no idea what your problem is.
That said, when I add the helper column & CF as per post#22 I get

Project75.xlsb
	D	E	F
1		ratings
2		EMN3	EMN
3		OS2	OS
4		FC2	FC
5		AM3	AM
6		AG3	AG
7		GM2	GM
8		STG2	STG
9		BM3	BM
10		AO	AO
11		ABF3	ABF
12		AM3	AM
13		LS1	LS
14		ET2	ET
15		ABF3	ABF
16		QM1	QM
17		LS2	LS
18		FCA2	FCA
19		CS3	CS
20		CTN2	CTN
21		CTT3	CTT
22		AZ1	AZ
23		MC1	MC
24		ABE2	ABE
25		STSCS	STS
26		MC1	MC
27		STG1	STG
28		HM3	HM
29		MT1	MT
30		FC2	FC
31		IT2	IT
32		BMC	BM
Sheet1

Cell Formulas
Range	Formula
F2:F32	F2	=IF(OR(E2={"sn","an","cn","fn","cs"}),E2,IF(OR(RIGHT(E2)={"1","2","3","c"}),LEFT(E2,LEN(E2)-1),IF(OR(RIGHT(E2,2)={"sn","an","cn","fn","cs"}),LEFT(E2,LEN(E2)-2),E2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E526	Expression	=COUNTIFS(Sheet2!$A$2:$I$57,F2,Sheet2!$B$2:$J$57,">0")	text	NO

 

[CA_Punit]

Please find the attached link file

Project75.xlsb

Microsoft Excel Workbook

1drv.ms

 

[Fluff]

@CA_Punit
Your code is still looking for partial matches & is therefore highlighting cells incorrectly.
For instance it's highlighting ET2 on row 14, but on sheet 2 ET has a value of 0

 

[CA_Punit]

I got it @Fluff
I have changed the Condition in my Sheet 2
See pic Below

So in my Case if E /ABF etc etc has any value in corresponding Column and if the search result is 1 then only TRUE will get reflected.
So if i search E in ABE and as it will result 3 it wont be counted and so it will result in FALSE.

 

[Fluff]

Your formula is highlighting ET2 on sheet1, because it starts with E, whereas it should only be highlighted if ET is greater than 0