none
compare excel columns RRS feed

  • Question

  • Hi,

    I want to compare two excel worksheet column by column. I have an excel spreadsheet(Worksheet 3) that contains 1 command button i.e. Compare.

    Now, If I click on Compare button it will compare Sheet1 and Sheet2 column by column.

    I want that A column of Worksheet 1 to be compared with A column of Worksheet2 and return the result to A column of Worksheet 4.

    Please refer the following for better understanding:


    Sheet1 Sheet2 Sheet4
    A B C A B C A B C
    100 400 700 100 400 700 TRUE TRUE TRUE
    200 500 800 200 500 800 TRUE TRUE TRUE
    300 600 900 400 800 600 FALSE FALSE FALSE

    The first row of Sheet1 and Sheet 2 contain column header.So I want validation should start from row 2.

    The same can be achieve through vlookup but I want a macro that can perform the desire action.

    Thanks!


    • Edited by Deb_chatt Tuesday, August 26, 2014 6:45 PM
    Tuesday, August 26, 2014 6:44 PM

Answers

  • For example:

    Sub Compare()
        Dim ws1 As Worksheet
        Dim ws4 As Worksheet
        Dim m As Long
        Dim n As Long
        Set ws1 = Worksheets("Sheet1")
        Set ws4 = Worksheets("Sheet4")
        m = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        With ws4.Range(ws4.Cells(2, 1), ws4.Cells(m, n))
            .FormulaR1C1 = "=Sheet1!RC=Sheet2!RC"
            .Value = .Value
        End With
    End Sub
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 26, 2014 7:47 PM
  • Try this version:

    Sub Compare()
        Dim ws1 As Worksheet
        Dim ws4 As Worksheet
        Dim m As Long
        Dim n As Long
        Set ws1 = Worksheets("Sheet1")
        Set ws4 = Worksheets("Sheet4")
        m = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        With ws4.Range(ws4.Cells(2, 1), ws4.Cells(m, n))
            .FormulaR1C1 = "=IF(Sheet1!RC="""","""",Sheet1!RC=Sheet2!RC)"
            .Value = .Value
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 26, 2014 9:01 PM

All replies

  • For example:

    Sub Compare()
        Dim ws1 As Worksheet
        Dim ws4 As Worksheet
        Dim m As Long
        Dim n As Long
        Set ws1 = Worksheets("Sheet1")
        Set ws4 = Worksheets("Sheet4")
        m = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        With ws4.Range(ws4.Cells(2, 1), ws4.Cells(m, n))
            .FormulaR1C1 = "=Sheet1!RC=Sheet2!RC"
            .Value = .Value
        End With
    End Sub
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 26, 2014 7:47 PM
  • Hi Hans,

    Thanks much for your reply.I have tried it and it's going towards the right direction.But a little problem is there. Suppose A column has 10 data and all other columns have number of data less than 10 then it is displaying true for those columns having Null value.

    The code that you have provided returning the following output:

    Sheet1 Sheet2 Sheet3
    1 1 10 1 1 1 TRUE TRUE FALSE
    2 2 2 2 2 2 TRUE TRUE TRUE
    3 3 3 3 3 3 TRUE TRUE TRUE
    4 4 4 4 4 4 TRUE TRUE TRUE
    5 5 5 5 5 5 TRUE TRUE TRUE
    6 6 6 6 6 6 TRUE TRUE TRUE
    7 10 7 7 7 7 TRUE FALSE TRUE
    8 8 8 8 TRUE TRUE TRUE
    9 9 TRUE TRUE TRUE
    10 10 TRUE TRUE TRUE

    But I want it will return the output as follows:

    Sheet1 Sheet2 Sheet4
    1 1 10 1 1 1 TRUE TRUE FALSE
    2 2 2 2 2 2 TRUE TRUE TRUE
    3 3 3 3 3 3 TRUE TRUE TRUE
    4 4 4 4 4 4 TRUE TRUE TRUE
    5 5 5 5 5 5 TRUE TRUE TRUE
    6 6 6 6 6 6 TRUE TRUE TRUE
    7 10 7 7 7 7 TRUE FALSE TRUE
    8 8 8 8 TRUE TRUE
    9 9 TRUE
    10 10 TRUE

    Please let me know what changes do I need to make to achieve the expected result.

    Thanks!

    Tuesday, August 26, 2014 8:49 PM
  • Try this version:

    Sub Compare()
        Dim ws1 As Worksheet
        Dim ws4 As Worksheet
        Dim m As Long
        Dim n As Long
        Set ws1 = Worksheets("Sheet1")
        Set ws4 = Worksheets("Sheet4")
        m = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        With ws4.Range(ws4.Cells(2, 1), ws4.Cells(m, n))
            .FormulaR1C1 = "=IF(Sheet1!RC="""","""",Sheet1!RC=Sheet2!RC)"
            .Value = .Value
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 26, 2014 9:01 PM