Solved

An Excel macro to compare two columns

Posted on 2013-12-21
6
3,048 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 47

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 47

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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