Text File Comparison

[Guest]

Hello:


I have 2 separate text file each containing a bunch of IP addresses.
The first text file (TextIP-1.txt) contains 100 IP addresses; list 1
below the other.
The second text file (TextIP-2.txt) contains 56 IP addresses; listed 1
below the other.

I am looking to compare the content of these 2 text files & identify those
overlapping IP addresses that exist in BOTH of these text files.
Maybe the results can be printed to a third text file. Any help appreciated.

* Is there a simple Excel function that can achieve this objective ?


Thanks in advance.
Jo.

 

[Harlan Grove]

I have 2 separate text file each containing a bunch of IP addresses.
The first text file (TextIP-1.txt) contains 100 IP addresses; list 1
below the other.
The second text file (TextIP-2.txt) contains 56 IP addresses; listed 1
below the other.

I am looking to compare the content of these 2 text files & identify those
overlapping IP addresses that exist in BOTH of these text files.
Maybe the results can be printed to a third text file. Any help appreciated.

....

This is text processing. While you could do it in Excel, there are
MUCH BETTER tools you could use. If you're running any version of
Windows NT (NT 4, 2000, XP, Vista), the console command FINDSTR using
command line option /G:<file> would be one superior alternative.

Still, this is an Excel newsgroup, so a pointless Excel approach: if
smaller file were imported into a single column range named IP and the
second file were imported into a different range with the first entry
in cell B2, then enter the following formula in C2,

=COUNT(MATCH(B2,IP,0))

and fill C2 down (or double-click its fill handle). These formulas
will evaluate to 1 for cells in B2:B# that match cells in the range
named IP and to 0 for cells with no match in IP. Autofilter B1:C#
selecting either 1 or 0 for the col C value, and paste the col B
results to blank worksheets, then save those blank workseets as text
files.

 

[RB Smissaert]

This can be done quite nicely with SQL.
Set a reference to Microsoft ActiveX DataObjects 2.x Library.
Have the 2 text files with IP addresses and with the headers IP_ADDRESS.
In this example both files are in C:\ but they can be in any (but the same)
folder.
Then run the following code:

Sub FindCommonIP()

Dim rs As ADODB.Recordset
Dim strTextConn As String
Dim strSQL As String

strTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties=Text;"

strSQL = "SELECT " & _
"F1.IP_ADDRESS " & _
"INTO ResultFile.txt IN " & _
"'C:\' " & _
"'Text;FMT=Delimited' " & _
"FROM " & _
"File1.txt F1 INNER JOIN File2.txt F2 ON " & _
"(F1.IP_ADDRESS = F2.IP_ADDRESS) "

Set rs = New ADODB.Recordset

rs.Open Source:=strSQL, _
ActiveConnection:=strTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Set rs = Nothing

End Sub

This will then produce a third text file holding the IP addresses that are
in
File1.txt and File2.txt.

RBS

 

[*alan*]

Hello:


I have 2 separate text file each containing a bunch of IP addresses.
The first text file (TextIP-1.txt) contains 100 IP addresses; list 1
below the other.
The second text file (TextIP-2.txt) contains 56 IP addresses; listed 1
below the other.

I am looking to compare the content of these 2 text files & identify those
overlapping IP addresses that exist in BOTH of these text files.
Maybe the results can be printed to a third text file. Any help
appreciated.

* Is there a simple Excel function that can achieve this objective ?


Thanks in advance.

If I correctly understand your objective to be getting rid of the
duplicates, then a pretty easy way to do so is as follows:

-Combine the 2 txt files into just one txt file.
-Open the combined txt file in Excel.
-highlight the column with the IP addresses

-Select "Data" > "Filter" > "Advanced Filter"

- Check the box "Unique records only"

- Hit OK

The duplicates will disappear.

 

[Guest]

Harlan:



Thanks a bunch to you & the other folks for the many suggestions. The SQL &
PowerShell solutions are obviously more robust & therefore more involved.

Actually, Harlan's solution (=COUNT(MATCH(B2,IP,0))) wins my vote for its
simple elegance in getting the job done.

Also, I found another solution (=IF(ISERROR (MATCH(B1, A:A, 0)), "No Match",
"Match") ) by Googling the appropriate key words.

I have already implemented both solutions. Just to double check.


* Harlan, how would you modify your code to format/highlight each cell
that is flagged with a match ? I am thinking about using green text on
yellow background with black border.
* Also, Harlan, it would be interesting to see some of the syntax
associated with the FINDSTR solution.



Thanks again everyone,
Jo.