Solved

File compare

Posted on 2011-09-19
6
177 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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