Solved

File compare

Posted on 2011-09-19
6
176 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
  • 4
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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

Expert Comment

by:Ingeborg Hawighorst
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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 earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

860 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