compare two sheets values in excel

Question

How can I compare one column's all values of a sheet-1 to another column values with different sheet-2 and if match then returns the value corresponding one of the columns of sheet-1 to another column of sheet-2 in excel?

Answer 1

Let's assume your values are in columns A of sheets named Sheet1 and Sheet2. Then, you can place the following formula into B1 of Sheet2 and drag down enough to cover you desired range: =IF(Sheet1!A1=Sheet2!A1,Sheet2!A1,"")

or, if you'e rather use VBA, place this code into a module:

Sub columnCompare()
Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range, r2 As Range
  Set sh1 = Worksheets("Sheet1")
  Set sh2 = Worksheets("Sheet2")
  Set r1 = sh1.Range("A1")
  Set r2 = sh2.Range("A1")
  While r1 <> "" And r2 <> ""
    If r1 = r2 Then r2.Offset(0, 1) = r1
    Set r1 = r1.Offset(1, 0)
    Set r2 = r2.Offset(1, 0)
  Wend
End Sub

Answer 2

If am understanding correctly, this is what you want,

Sheet1

Sheet2

Enter the below formula in B2 of sheet2 and drag down as in the image,

=INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0),1)

Answer 3

I can only answer part of your question: comparing two columns and detecting that they differ.

You have an excellent tutorial answer for that in Tony M's answer, above.

However, this will perform very slowly on a large data set, because:

1. Reading a range one cell at a time is very slow;
2. Comparing values pair-by-pair is inefficient, especially for strings, when the number of values gets into the tens of thousands,

Point(1) is the important one: it takes the same amount of time for VBA to pick up a single cell using var = Range("A1") as it does to pick up the entire range in one go using var = Range("A1:Z1024"); and every interaction with the sheet takes four times as much time as a string comparison in VBA, and twenty times longer than an comparison between floating-point decimals; and that, in turn, is three times longer than an integer comparison.

So your code is probably four times faster, and possibly a hundred times faster, if you read the entire range in one go, and work on the Range.Value2 array in VBA.

That's in Office 2010 and 2013 (I tested them); for older version of Excel, you'll see quoted times between 1/50^th and 1/500^th of a second, for each VBA interaction with a cell or range of cells. That'll be *way** slower because, in both old and new versions of Excel, the VBA actions will still be in single-digit numbers of microseconds: your code will run at least a hundred times faster, and probably thousands of times faster, if you avoid cell-by-cell reads from the sheet in older versions of Excel.

So big gains are there to be made - an interval perceptible to the user - in picking up the ranges in a single 'hit' and then performing the comparison on each item of an array in VBA.

arr1  = Range1.Values
arr2  = Range2.Values

' Consider checking that the two ranges are the same size

For i = LBound(arr1, 1) To Ubound(arr1, 2) 

    For j = LBound(arr1, 2) To Ubound(arr1, 2)

        If arr1(i, j) <> arr2(i, j) Then
            bMatchFail = True
            Exit For
        End If

    Next j

    If bMatchFail Then Exit For

Next i

Erase arr1
Erase arr2

You'll notice that this code sample is generic, for two ranges of the same size taken from anywhere - even from separate workbooks. If you're comparing two adjacent columns, loading a single array of two columns and comparing IF arrX(i, 1) <> arrX(i,2) Then is going to halve the runtime.

Your next challenge is only relevant if you're picking up tens of thousands of values from large ranges: there's no performance gain in this extended answer for anything smaller than that.

What we're doing is:

Using a hash function to compare the values of two large ranges

The idea is very simple, although the underlying mathematics is quite challenging for non-mathematicians: rather than comparing one value at a time, we run a mathematical function that 'hashes' the values into a short identifier for easy comparison.

If you're comparing ranges against a 'reference' copy, you can store the 'reference' hash, and this halves the workload.

There are some fast and reliable hashing functions out there, and they are available in Windows as part of the security and cryptography API. There is a slight problem in that they run on strings, and we have an array to work on; but you can easily find a fast 'Join2D' function that gets a string from the 2D arrays returned by a range's .Value2 property.

So a fast comparison function for two large ranges will look like this:

Public Function RangeCompare(Range1 as Excel.Range, Range2 As Excel.Range) AS Boolean
' Returns TRUE if the ranges are identical.
' This function is case-sensitive.
' For ranges with fewer than ~1000 cells, cell-by-cell comparison is faster

' WARNING: This function will fail if your range contains error values.

RangeCompare = False

If Range1.Cells.Count <> Range2.Cells.Count Then
    RangeCompare = False
ElseIf Range1.Cells.Count = 1 then
    RangeCompare = Range1.Value2 = Range2.Value2
Else
    RangeCompare = MD5(Join2D(Range1.Value2)) = MD5(Join2D(Range2.Value2))
Endif

End Function

I've wrapped the Windows System.Security MD5 hash in this VBA function:

Public Function MD5(arrBytes() As Byte) As String
' Return an MD5 hash for any string

' Author: Nigel Heffernan Excellerando.Blogspot.com

' Note the type pun: you can pass in a string, there's no type conversion or cast
' because a string is stored as a Byte array and VBA recognises this.

    Dim oMD5 As Object  'Set a reference to mscorlib 4.0 to use early binding


    Dim HashBytes() As Byte
    Dim i As Integer


    Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    HashBytes = oMD5.ComputeHash_2((arrBytes))
 
    For i = LBound(HashBytes) To UBound(HashBytes)
        MD5 = MD5 & Right("00" & Hex(HashBytes(i)), 2)
    Next i


   Set oMD5 = Nothing ' if you're doing this repeatedly, declare at module level and persist
   Erase HashBytes


End Function

There are other VBA implementations out there, but nobody seems to know about the Byte Array / String type pun - they are not equivalent, they are identical - so everyone codes up unnecessary type conversions.

A fast and simple Join2D function was posted by Dick Kusleika on Daily Dose of Excel in 2015:

Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String

    Dim i As Long, j As Long
    Dim aReturn() As String
    Dim aLine() As String

    ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1))
    ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2))

    For i = LBound(vArray, 1) To UBound(vArray, 1)
        For j = LBound(vArray, 2) To UBound(vArray, 2)
            'Put the current line into a 1d array
            aLine(j) = vArray(i, j)
        Next j
        'Join the current line into a 1d array
        aReturn(i) = Join(aLine, sWordDelim)
    Next i

    Join2D = Join(aReturn, sLineDelim)

End Function

If you need to excise blank rows before you make the comparison, you'll need the Join2D function I posted in StackOverflow back in 2012.

The most common application of this type of hash comparison is for spreadsheet control - change monitoring - and you'll see Range1.Formula used instead of Range1.Value2: but your question is about comparing values, not formulae.