?
Solved

Excel compare data 2 columns, results 3rd column

Posted on 2013-05-15
10
Medium Priority
?
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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