How to know if cell value doesn't exist in another sheet

[satya12]

Hi All,

Here i am having 2 excel files. i have to take the value from excel 1 and filter that value in excel 2 , if the value doesn't exist in excel2 , add the color in excel1 .
excel 2 values will start on A3 , we have to filter the value in A column in excel 2.

Please help me with this.

 

[Toadstool]

Hi Satya12,

I'm not sure if I fully understand, more details with examples would be good, and the word "filter" can mean something specific in Excel but I'm going to ignore that and give a simple example.

You say "add the color in excel1" and that suggests a Conditional Format (CF) but CF won't let you refer to another workbook. For that reason I've added a work column in the 1st workbook to do the check and the highlight is based on that result.

Please let me know if this is anything like your requirement.

Satya-B.xlsx
	A
1	This is Excel B
2
3	Goat
4	Sheep
5	Pig
6	Mole
7
8
9	Vole
10
11
12
13
Sheet B

Satya-A.xlsx
	A	B
1	This is Excel A	Test
2	Dog	TRUE
3	Cat	TRUE
4	Goat	FALSE
5	Sheep	FALSE
6	Horse	TRUE
7	Pig	FALSE
8	Mole	FALSE
9	Vole	FALSE
10	Chicken	TRUE
11		FALSE
12		FALSE
13		FALSE
14		FALSE
Sheet A

Cell Formulas
Range	Formula
B2:B14	B2	=AND(A2<>"",ISNA(MATCH(A2,'[Satya-B.xlsx]Sheet B'!$A$3:$A$9999,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9999	Expression	=AND(A2<>"",$B2)	text	NO

 

[satya12]

Thanks for your help,
What I mean is in the excel 2 that has already filters in the file, what we have to do is we have to check the value in the A col from excel 1 ..we have to the value from excel 1 and search the value in the A col filter , if value is not match in the excel 2 A col filter ..we have to add the color in that cell in excel 1
If you see in the 1st image and 2nd images some of values are not there in 2nd image.in the 2nd image for A col filter already there in the file itself. We have to search those value from 1 st image, if values not found in the 2nd image. We have color the cell in 1st image which value not found in the 2nd image.
I think it's clarify your doubts

 

[Toadstool]

No, I'm afraid that doesn't clarify to me but maybe others can understand.

Did my example get close?

 

[satya12]

No,i think your example doesn't work

If you see this image this is Excel 1 we are taking the value from this file and filter it in excel 2

This is the excel2 if you see A col has already filter . for example if you see in the excel 1 H5 value is not there excel 2 .
in the moment macro has do
apply color in that value H5 in excel1 ,that output look like this

 

[Toadstool]

Satya-Excel1.xlsx
	A
1	PL
2	H5
3	I6
4	N2
5	L9
6	YU
7	KO
8	NH
9	MC
10
Sheet B

Satya-Excel2.xlsx
	A	B
1	PL	Test
2	H5	TRUE
3	I6	FALSE
4	N2	FALSE
5	L9	FALSE
6	YU	FALSE
7	KO	FALSE
8	NH	FALSE
9	MC	FALSE
10		FALSE
Sheet A

Cell Formulas
Range	Formula
B2:B10	B2	=AND(A2<>"",ISNA(MATCH(A2,'[Satya-Excel1.xlsx]Sheet B'!$A$3:$A$9999,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9999	Expression	=AND(A2<>"",$B2)	text	NO