Solved

Excel compare data 2 columns, results 3rd column

Posted on 2013-05-15
10
555 Views
Last Modified: 2013-08-12
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.
0
Comment
Question by:deanind
  • 4
  • 3
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39169139
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
 

Author Comment

by:deanind
ID: 39169167
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39169210
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:deanind
ID: 39169557
I added =A5=C8 to all bank cells, it created FALSE. This did not work. Any other ideas I can try?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39169589
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
 

Author Comment

by:deanind
ID: 39169619
That WORKED. Thank You for your assistance/
0
 

Author Comment

by:deanind
ID: 39382109
This worked perfectly.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now