Compare two text files using vba arrays

**YasserKhalil** · 12-15-2016, 07:21 PM

Hello everyone
I have two text files and I need to compare between them

I have a text file which each line has some words/abbreviations. (1st file)
I want this file to be compared with another file which has another list of words. (2nd file)

Go through each line of 1st and see if any words/abbreviations on that particular line has a match in the 2nd file. If at least one of the words/abbreviations has a match, copy that line to a new 3rd text file.
If that row has no matches on the 2nd file, copy that line to a new 4th text file.

Thanks advanced for help

**kasan** · 12-15-2016, 08:01 PM

Hi,
Are your real files contains this kind a rows like attached files? You mentioned words/abbreviations in each file, but looks like it's just arrays of symbols.
Is there any logic how words/abbreviations can be split in each line? To be more specific - do you expect that "ASHTO" will be recognized as match in first lines of both files?

**YasserKhalil** · 12-15-2016, 08:49 PM

Thanks a lot for reply. To see the text files better, you should open them using Notepad Plus Plus ..
It is similar to lists

**YasserKhalil** · 12-16-2016, 08:51 AM

Any help in this topic please

**kasan** · 12-16-2016, 09:51 AM

I've done something like this, but with two excel sheets.
First thing I did - created Scripting.Dictionary and filled it with all words/abbreviations from file1. Thats why I asked about how does words/abb seperated in your files. Words extraction from line can be done with Split().
Then I took first line from file2 and loop throgh all Dictionary records and with Instr() found out if any words from file1 are in this line. In your case at the end of this loop (for first line of file2) - line should be copied to File3 (if found match) or File4 (no match).
And this way throgh all lines in file2.
If you think logic like this could help you - let me know, I'll share my excel-compare file.

**dflak** · 12-16-2016, 11:06 AM

Maybe VBA is not the best tool for this job. This wheel has already been invented in Unix - Windows versions of the command are available. https://www.gnu.org/software/diffutils/

**YasserKhalil** · 12-16-2016, 12:51 PM

Thanks a lot for replies
@Mr. Kasan thanks a lot for your idea. It will be better to have your excel file. May be then we can find a way to import the data from text files into excel and then export later
@Mr. dflak thanks for reply . I will give it a try

**kasan** · 12-16-2016, 01:25 PM

File to compare two excel sheets (in different files) with identical sheet structure. This one does two things:
1) Change color of cell if File1.Cell <> File2.Cell
2) Change color of specific changed line in a cell (in case cell contains multi-line text).
May be will be useful.

I tried to read your text files and compare them.
Split was made only by "vbLf" - thats mean for compare I used "A3", "A3 Thinking" ... from File1. If you need to compare "A3", "Thinking" - then another split is needed.
Anyway, test it.

Please Login or Register  to view this content.

**YasserKhalil** · 12-16-2016, 04:40 PM

Thank you very much for your help and great reply
I tried to adapt it to suit my text files and get them work but couldn't ..

**kasan** · 12-16-2016, 04:51 PM

I hope you didn't try to read your files with attached file? This one is for excel files only.
Try to test code from post #8.

**YasserKhalil** · 12-17-2016, 12:01 AM

But excel reads these text files in your post #8 in proper way I think and it appears in proper structure in column A for each file ...

**kasan** · 12-17-2016, 04:48 AM

Try this file. You need to change paths to all files in code.
Just one correction in code from #8 - variable flag should be 0 for each step by i.
Test it.

Please Login or Register  to view this content.

**YasserKhalil** · 12-17-2016, 07:22 AM

Thanks a lot for this code and I think it is too closer >>>
I will attach simple text files to test on so as to be more clearer

In the first text file there are five items:
Yasser
Ahmed
Reda
Khalil
Laila

And in second text file there are four items for exmaple :
Ahmed
Khalil
Salah
Yasser

The 3rd text file would contain items that has match in 2nd file so the the 3rd text file would contain :
Yasser
Ahmed
Khalil

The 4th text file would contain the items that has no match so the result would be :
Reda
Laila

Thank you very much

**YasserKhalil** · 12-17-2016, 07:24 AM

When testing post #12 the 3rd text file contained :
Yasser
Khalil

While the 4th file
Ahmed
Reda
Laila

So the item Ahmed is missing from 3rd text file and existed in 4th text file (that's the only fault now)

**kasan** · 12-17-2016, 11:23 AM

I got it, my bad, index in array starts with 0, not 1
Please change this line in last code (post #12):

Please Login or Register  to view this content.

to this line:

Please Login or Register  to view this content.

Worked for me on your last sample files.

**YasserKhalil** · 12-17-2016, 11:46 AM

I was thinking of changing it but the electricity was off at this moment. You got it and thank you very very much for great help in this issue
Best and kind regards

**kasan** · 12-17-2016, 12:22 PM

Thanks for feedback & reputation points

Compare two text files using vba arrays

LinkBack

Thread Tools

Rate This Thread

Display

Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Re: Compare two text files using vba arrays

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compare two Text files : Multiple Look Up criteria

Compare two arrays

Excel Macro to compare contents of text files in two folders which has same name

How to compare text files in excel (data pulled in with VLOOKUP)

vba compare arrays and remove exact matching arrays

Compare 2 Arrays

Compare 4 text files using Macro

Bookmarks

Bookmarks

Posting Permissions