Comparing on 2 worksheets

  • Thread starter Thread starter Roman
  • Start date Start date
R

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
 
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?
 
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...
 
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.
 
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....
 
Back
Top