Solved

Excel compare data 2 columns, results 3rd column

Posted on 2013-05-15
10
556 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Backup Your Microsoft Windows Server®

Backup 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.

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

16 Experts available now in Live!

Get 1:1 Help Now