?
Solved

An Excel macro to compare two columns

Posted on 2013-12-21
6
Medium Priority
?
3,482 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
[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
  • 2
6 Comments
 

Author Comment

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

Accepted Solution

by:
Martin Liss earned 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 49

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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