?
Solved

File compare

Posted on 2011-09-19
6
Medium Priority
?
179 Views
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.  
0
Comment
Question by:nav2567
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 50
ID: 36564468
Hello,

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

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

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

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

=B1

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

cheers, teylyn
0
 

Author Comment

by:nav2567
ID: 36564509
let's say if my book1.xls has:
apple
bee
cat
dog in column A, and my book2.xls has:
bee
moose
apple
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.



0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 50
ID: 36564567
Hello,

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

Book1.xls
Book2.xls
0
 

Author Comment

by:nav2567
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  

Thanks.
0
 
LVL 50

Accepted Solution

by:
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")
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

764 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