Solved

An Excel macro to compare two columns

Posted on 2013-12-21
6
2,555 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 45

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 45

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
combo box dependant userform submit button 24 28
Excel formula 6 21
From where do I download MySQL for Excel? 2 10
onOpen 14 38
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

11 Experts available now in Live!

Get 1:1 Help Now