File compare

Posted on 2011-09-19
Medium Priority
Last Modified: 2012-08-14
I have two txt files.  File A contains over 500 names.  File B contains about 100 names.  

Is there a utility out there which allows me to highlight all names in file A which appear in File B?

Please advise.  
Question by:nav2567
  • 4
  • 2
LVL 50
ID: 36564468

with both files open, enter this formula into Book1, where the other file is named Book2 and the names are in column A in each file:

=IF(ISNA(MATCH(A1,'[Book2.xls]Sheet1'!$A:$A,0)),"not found", "matched")

copy down.

cheers, teylyn

LVL 50
ID: 36564474
You can also use a formula like this in column B


Then select column A and enter a conditional formatting using this formula


Select a format and hit OK. All matched names will now have the format you chose. You can hide column B.

cheers, teylyn

Author Comment

ID: 36564509
let's say if my book1.xls has:
dog in column A, and my book2.xls has:
tiger in column A.  
I'd like my book1.xls column B displays in a way like this:
apple  match
bee     match
cat      not match
dog     not match
I tried to put the following in B1 of book1 but did not work:
=IF(ISNA(MATCH(A1,'[c:\test\Book2.xls]Sheet1'!$A:$A,0)),"not found", "matched")

Please advise again.  Thanks.

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 50
ID: 36564567

see attached Book1 and Book2

With both workbooks open, the formula is

=IF(ISNA(MATCH(A1,[Book2.xls]Sheet1!$A:$A,0)),"not match", "match")

Save both files. When you now close Book2, the formula will change to include the correct path name automatically.

cheers, teylyn


Author Comment

ID: 36564708
Sorry.  I still cannot get it to work.  

File one has a lot more names than file two.  The goal is to see the names in file 2 show "matched" in file one  

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36564718
Well, put the formula in the other file then. :-)

Make sure that you use the correct reference to Book1 instead of Book2!!

=IF(ISNA(MATCH(A1,[Book1.xls]Sheet1!$A:$A,0)),"not match", "match")

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial is about creating a new Microsoft Online User Profile account along with how to transfer your files and settings. You may be faced with this situation if your existing user profile has become corrupted.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question