How do I Compare two text files, with same information but different rows

Question

I am a really big fan of Beyond Compare and Notepad ++ and use it at my work for many comparisons on a daily basis. I am using the latest version software. So this is my question. I have two Text files, Where as text file 1 has the following (Account ID's ) The number is the one I really want to compare

Text|Text|1234532|Text
Text|Text|1234678|Text
Text|Text|2345612|Text
Text|Text|3427891|Text
Text|Text|0235719|Text
Text|Text|3421482|Text

And Now here is my second text fIle 2 has the following (Account ID's ) The number is the one I really want to compare

Text|Text|0235719|Text
Text|Text|3421482|Text
Text|Text|3427891|Text
Text|Text|2345612|Text
Text|Text|1234678|Text
Text|Text|1234532|Text

The text and numbers above are delimited by '|' If you look all the above mentioned numbers (the ones I really want to compare ) are the same but they are in a different row in each file.

So is there anyway to compare both the files and get the results as desired. My desired results are that file 1 should show the numbers are mmatching in file 2.

Thank you very much in advance.

I understand the comparison, but not the result. Do you want an excel spreadsheet with only the numbers that appear in both files (thus the excel tag)? Or "that file 1 should show" _only_ the numbers that match, but still with text and delimiters? — UndeadBob, Feb 01 '17 at 16:03
It sounds like what you really need are command line tools like diff or grep with regex. They'd probably be much more suitable and efficient than anything you could do in Excel. — xarxziux, Feb 01 '17 at 21:37
@paravav I have turned the `files` into `code`. Please check that is what you want — Bruce Martin, Feb 01 '17 at 21:45

Bruce Martin · Answer 1 · 2017-02-06T03:19:15.173

Introduction

When fields to compare are in different sequence, use a Sort on the compare fields to get then into the same sequence !!!

Batch

A basic approach of

Extract the relavent columns from the 2 files to 2 new files
Sort the new files
Compare the files

Should work. This could be achieved with:

*nix tools
There are Csv specific tools they may be able to do it. Try googling "csv utilities" etc
Programming (e.g. python) Just do a Sorted-Key-File-Merge type processing

Gui Tools

Beyond Compare

Looking at the Beyond compare documentation, It has sorting capabilities. Have a closer look at what Beyond Compare can do.

Csv Editors

Some of the Csv-Editors should be able to handle this. Again take a copy of the files (It would be an advantage to add column headers if there are none).

In ReCsvEditor

You can sort a file by either clicking on a column heading or using Data >>> Sort dropdown menu
In the ReCsvEditor the file compare allows you to do column compares

ReCsvEditor compare