CODEWITHSUNDEEP
excelexcel-2007vba
Shad Khan
Shad Khan

Reputation: 119

I want to check whether one column values are present in another sheets column in excel?

There is a column in one of my sheet with 30 locations each location having 12 codes Ex

LOC CODE
1     A
1     B
1     C 
1     D
2     A
2     B
2     C
2     D

There is another sheet with only one column

CODE 
A 
B
C
D

I want to find out whether the all codes in 2nd column of first sheet are present in second sheet. Also I want to know whether all the codes in sheet 2 are present in sheet 1 or not. How can I do that in excel?

Upvotes: 1

Views: 957

Answers (2)

pnuts
pnuts

Reputation: 59495

If you want to know not just that all are present but which, if any, are not then a formula such as: 

=IF(MATCH(B2,Sheet1!B:B,0)>0,"present")

in C2 and copied down may suit, assuming your codes are in ColumnB in each sheet and that Sheet1 is adjusted to suit. Where not present the result would show #N/A.

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Let your codes be in Sheet1 in column B, and in Sheet2 in range A1:A10, then:

1)

I want to find out whether the all codes in 2nd column of first sheet are present in second sheet.

=SUM(IF(Sheet1!B:B<>"",ISNA(MATCH(Sheet1!B:B,Sheet2!A1:A10,0))*1))=0

2)

Also I want to know whether all the codes in sheet 2 are present in sheet 1 

=SUM(IF(Sheet2!A1:A10<>"",ISNA(MATCH(Sheet2!A1:A10,Sheet1!B:B,0))*1))=0

Both formulas returns TRUE/FALSE

NOTE, since it's array formulas you should evaluate them by pressing CTRL+SHIFT+ENTER

Upvotes: 2

Related Questions