Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

An Excel macro to compare two columns

Posted on 2013-12-21
6
Medium Priority
?
3,801 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 50

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

 

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 50

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

810 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