Comparing on 2 worksheets

[Roman]

Hi,

I have 2 different Excel files with data. There are Numbers in Column 1 in
both files, and names in Column 2. Other then writing VB script or buying a
third party product is there an easier way to:

Check to see what numbers in File 1 are not in File 2.

If not, I can copy past the worksheet into the same file to have 2
worksheets in 1 file. If I do this, what would the formula be to check this
and then output the numbers that are missing in column 1 between the 2
worksheets. To get fancy what if I wanted it to then tell me the name in
Column B for the missing numbers.

Thanks

 

[Sheeloo]

See http://www.cpearson.com/excel/ListFunctions.aspx for ideas...

 

[Roman]

Thanks but the information says the columns have to be the same length. The
2 columns I am trying to compare are different lengths....

Any other suggestions?

 

[Sheeloo]

For simpilicity let us assume you have numbers and names in sheet 1 and sheet
2 of the same file.
Enter this in C1 of Sheet2 and copy down
=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not in List","Found in List")

This will put "Found in List" against those nos which are found in Sheet1
Col A.

Assuming no is present in Sheet 1 then this will return the name against the
number in Sheet 2 if entered in D1 and copied down
=Vlookup(A1,Sheet1!A:B,2,False)

Hope this gives you enough ideas to solve your problem...

 

[ShaneDevenshire]

Hi,

Try this formula:

=COUNTIF(Test.xls]Sheet1!$A$1:$A$6,A1)

Copy it down.
This formula returns 0 if the value in A1 is not found in the second sheet,
otherwise it return the number of times the number is found.

If this helps, please click the Yes button.

 

[Roman]

Thanks...

I added this line to the C1 cell on sheet 2, and it does not seem to work.
All the cells are "not on list. I verified that on sheet 1 and sheet 2 A1
has 10001 as the number.

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"not on list","found")

What am I doing wrong? Thanks....