Solved

File compare

Posted on 2011-09-19
6
178 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
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
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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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