Duplicate Indicator Macro Trouble

Hi Folks,

Hoping you can help me. I'm sure it's something basic I'm not understanding (I'm pretty new at this so be gentle!).

I have 3 sets of data (but let's start with just 2) that I want to compare against each other to find duplicates.

The data is text from word/text/web files.

After compiling this data into columns, I am attempting to either cut/paste or import the data into separate columns in the same excel sheet so that I can use the following macro to discover the duplicates in each column.

However, for some reason, although I know that there are duplicates in the data, the macro isn't finding them.

I've discovered that if I cut/paste a cell from one column to the next (overwriting the duplicate data) it will find them.
I've discovered that if I were to type the data manually, it will find the duplicates.

SO, it seems that there's an issue with the data being copied/pasted or the way I am importing it.
I've tried saving the MS Word documents as .txt files so that I can import them.
I've tried ensuring that the formatting of the text is exact, including font, size, capitalization etc.

Any help would be much appreciated.

Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant
str1 = InputBox("Enter Column Name to be Compared")
str2 = InputBox("Enter Column Name to Compare")
str3 = InputBox("Enter Column Name to put the Result")
Range(str1 & "1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range(str1 & "1:" & Selection.Address)
Range(str2 & "1").Select
Selection.End(xlDown).Select
Set CompareRange = Range(str2 & "1:" & Selection.Address)
I = 1
To_Be_Compared.Select
For Each x In Selection
For Each y In CompareRange
If x = y Then
Range(str3 & I).Value = x
I = I + 1
End If
Next y
Next x
End Sub

Thanks aboutsetta!

Unfortunately your macro didn't show any results either, but I will keep it on file for future use.
When I compare the lists to themselves, these macros work to find duplicates, however when compared to different lists, they find nothing?

I'm wondering if it's a problem with the way my data is formatted.

Is there a particular way one should format the data for best results? (font, size, should they be the same?)
Does it have to be in alphabetical order?
Is it better to cut/paste your data or import it via text file? Or does it have to be manually typed in order for the macros to work?


I'm extremely confused why these 2 macros that really do work wont work on the lists I'm trying to compare...

Any ideas are appreciated.

Thanks

Thanks aboutsetta.

Here are the 2 files. I happen to know that there is duplicate data - Complete Wellhead & Valve Servicing Ltd. - is one of them.

Thanks so much for your help!

Thanks aboutsetta, this will save me days of searching and testing. Worked exactly as I needed it to.

You are awesome!