Extracting matched and unmatched records from two text files

Hi, Everyone

I have two pipe delimited text files. I need to extract matched records from these two files and unmatched records only from one (based on combination of two fields present in both files, uniquely identifying each record), and want these records in a separate third file. Both these files have over 80,000 records. I wrote a macro but it takes too much of time to execute. How can this be done with macro in an efficient way, that saves time? Also, Is there any useful method or procedure of excel/macro that can do this job quickly? Please help.

Hi xladept,

Thanks, for the quick reply .

I am afraid that, i think i followed the dictionary approach in the code, but am not sure about the Range union thing. And I am still not sure how to do for 80,000 records .

I have attached a sample workbook in the BEFORE and AFTER sheets format as suggested by you. It contains enough data for what i am trying to do.

Hi xladept,

Thanks for the solution. I really appreciate that.

I get that, it does pretty much the same what i wanted.

But here is the problem .
In excel it looks pretty much good to handle, but what do i need to do if i have to do it originally in the pipe delimited text files (File 1 and File 2) which will be used as input and Write that to the third text file (this also should be pipe delimited). I have attached a text file which contains the macro i am using.

Please have a look and suggest the ways of improvement.

Hi, Xladept

Originally Posted by xladept

I can't test it so it, if it works at all, will need some tweaking - Let me know!

Please Login or Register  to view this content.

When i executed, it is showiing error msg, "subscript out of range". I debugged, it is in the line
Set w3=Workbooks(file3).Sheets("Sheet1").

I guess it has something to do with, the file3 in the bracket.
What needs to be changed here?

And just to remind, i work on excel 2003 version, so hope it has nothing to do with that.

Also, in excel 2003, max range allowed is 65536, so would it pose a problem, if my file file1, file2 have over 80-90,000 records.