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
Solved

An Excel macro to compare two columns

Posted on 2013-12-21
6
2,913 Views
Last Modified: 2013-12-21
I am looking for an Excel macro or utility which can automate the following procedure which I commonly use.  Here is the scenario:

I have data in Sheet 1 and in Sheet 2.  I need to compare data in a specific column of Sheet 1 against data in a column in Sheet 2.  I need to find data which is in Sheet 1 but not in Sheet 2; and data in Sheet 2 not in Sheet 1.

I would like the macro to:
1)      prompt which column in Sheet 1 and which column in Sheet 2 I want to compare.  This allows me to enter: A or B or whatever
2)      list out on Sheet 3 data in Sheet 1 not on Sheet 2
3)      list out on Sheet 4 data in Sheet 2 not on Sheet 1

Please note that the two columns I may be comparing can be all numeric, or all alpha, or all alpha-numeric; but no mixtures of different data types.

I am attaching an example file.

Thanks.
Compare.xlsx
0
Comment
Question by:ParaGlow
  • 4
  • 2
6 Comments
 

Author Comment

by:ParaGlow
ID: 39733667
Pls make sure the solution works for "big data".
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39733774
Sub Compare()

Dim lngRow As Long
Dim FoundIt As Range
Dim strSheet1ColLtr As String
Dim strSheet2ColLtr As String
Dim intSheet1ColNum As Integer
Dim intSheet2ColNum As Integer
Dim lngNewRow As Long
Dim intCol As Integer

strSheet1ColLtr = InputBox("Please enter the column letter you want to compare on Sheet1", "Sheet1 Column Letter", "A")
strSheet2ColLtr = InputBox("Now please enter the column letter you want to compare on Sheet2", "Sheet2 Column Letter", "A")
If IsNumeric(strSheet1ColLtr) Or _
   IsNumeric(strSheet2ColLtr) Or _
   Trim(strSheet1ColLtr) = "" Or _
   Trim(strSheet2ColLtr) = "" Then
    MsgBox "Invalid column letter selections"
    Exit Sub
End If

intSheet1ColNum = Sheet1.Columns(strSheet1ColLtr).Column
intSheet2ColNum = Sheet1.Columns(strSheet2ColLtr).Column

Application.ScreenUpdating = False

Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1:E1").Select
Selection.Copy

Sheets("Sheet3").Select
ActiveSheet.Range("A1:E1").Select
ActiveSheet.Paste

Sheets("Sheet4").Select
ActiveSheet.Range("A1:E1").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Application.CutCopyMode = False

lngNewRow = 2
For lngRow = 2 To Sheet1.UsedRange.Rows.Count
    With Sheet2.Columns(intSheet2ColNum) ' Range("C:C")
        Set FoundIt = .Find(What:=Sheet1.Cells(lngRow, intSheet1ColNum).Value, LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If FoundIt Is Nothing Then
            With Sheets("Sheet3")
                For intCol = 1 To 5
                    .Cells(lngNewRow, intCol) = Sheet1.Cells(lngRow, intCol)
                Next
                lngNewRow = lngNewRow + 1
            End With
        End If
    End With
Next

lngNewRow = 2
For lngRow = 2 To Sheet2.UsedRange.Rows.Count
    With Sheet1.Columns(intSheet1ColNum) ' Range("C:C")
        Set FoundIt = .Find(What:=Sheet2.Cells(lngRow, intSheet2ColNum).Value, LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If FoundIt Is Nothing Then
            With Sheets("Sheet4")
                For intCol = 1 To 5
                    .Cells(lngNewRow, intCol) = Sheet2.Cells(lngRow, intCol)
                Next
                lngNewRow = lngNewRow + 1
            End With
        End If
    End With
Next

Application.ScreenUpdating = False

End Sub

Open in new window

0
 

Author Comment

by:ParaGlow
ID: 39733797
I will "test" it out and post feedback but I can already tell that you know your onions!!  Thanks a bunch.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ParaGlow
ID: 39733809
Works flawlessly.  I will need to test it out on larger files.  I cannot thank you enough.
0
 

Author Closing Comment

by:ParaGlow
ID: 39733811
Awesome stuff.  Stay cool.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39733812
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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