Solved

Excel compare data 2 columns, results 3rd column

Posted on 2013-05-15
10
558 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

827 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