Excel compare data 2 columns, results 3rd column

Have 2 lists of alpha/numeric part nos and would like to put duplicates in a 3rd column. Then we could id those part nos that are not dupes. Col A is 1st data set & col C is compare data set.
Tried using VBA from MS forum, works on field of 5 data sets. Tried on 1600 data sets, will not work:

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C1600")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

Select range: A1:A1600
Run tools > macro > find_matches
Results of matches s/be in col B

Using Excel 2003, can use 2007 on another computer.
deanindAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
A5 and C8 were supposed to be same cells. If you have same cells elsewhere then change the addresses accordingly. If the equal cells show false then it means that they are NOT equal. If this is the case then try changing

 If x = y Then x.Offset(0, 1) = x

to

 If trim(x) = trim(y) Then x.Offset(0, 1) = x

If there is still a problem then upload a sample file showing your problem.
0
 
Saqib Husain, SyedEngineerCommented:
Can you further explain "does not work"?

This code should work just fine. What it is supposed to do is repeat the value from column A to column B whenever that value is found in column C.
0
 
deanindAuthor Commented:
Yes, when I run the code, it takes several minutes and their are no dupes in col B. Col A has  1638 records and col C has 1610 records. There are 28 non dupe records we are trying to id.
All the records have non A/N chrs, such as "-", could this have an impact on the matches?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
If A5 and C8 are supposed to have same data then in a blank cell enter this formula

=A5=C8

If the values are equal then the formula would return TRUE. If this is the case then your code should pick this up.

1600 items of data compared with another 1600 items of data would mean well over 2.5 million comparisons and makes sense that it takes some time.
0
 
deanindAuthor Commented:
I added =A5=C8 to all bank cells, it created FALSE. This did not work. Any other ideas I can try?
0
 
deanindAuthor Commented:
That WORKED. Thank You for your assistance/
0
 
deanindAuthor Commented:
This worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.