none
Compare two column from two different sheets

    Question

  • I would compare column B of sheet 2(test) with column B sheet 1(board) .

    For e.g. if the content in test sheet , cell 1 of column B is “C1”, it should look for “C1” in the column B of board sheet. “c1” can be in any row in column of board sheet . It isn’t necessary for the contents to be in the same row in both sheets.

    So if “c1 ” is located in column of both sheets then,

    1)      In board sheet, In the next cell that contains “c1 ” insert “Yes

    For e.g. if “c1” is found in B22 of board sheet then insert “yes ” in C22 of board sheet

    2)      In Test Sheet, delete the entire row that contains “c1

    If the content in one of the column B of test Sheet, is not in Board sheet of column B then do nothing

    This should be done for the entire column B              

    I have inserted a picture for better illustration

    http://www.iimmgg.com/image/6c9057ee8d05f91f5eda558918ab5192

    I have also attached a sample workbook,

    http://www.mediafire.com/?nta5h26c42vkfc2

    Inside the workbook,

    Board sheet and test Sheet are the sheets that need to be compared

    Button sheet have a button where the codes should be included such that when the button is clicked it should compare the sheets

    SampleBoard sheet is how the board sheet should look like after comparing

    FinalTest sheet is how the test sheet should look like after comparing

    Monday, November 08, 2010 3:29 PM

Answers

  • Based on your description - assumes that columns C of both sheets are otherwise empty:

    Sub Macro1()
        With Sheets("Board")
            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))
            .FormulaR1C1 = _
            "=IF(NOT(ISERROR(MATCH(RC[-1],Test!C[-1],FALSE))),""Yes"","""")"
            .Value = .Value
            End With
        End With
        With Sheets("Test")
            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))
            .FormulaR1C1 = _
            "=IF(ISERROR(MATCH(RC[-1],Board!C[-1],FALSE)),""keep"",""Delete"")"
            .Value = .Value
            End With
            .Range("B1", .Cells(Rows.Count, 3).End(xlUp)).Sort _
                Key1:=.Range("C1"), _
                Order1:=xlDescending, Header:=xlNo
            Set myC = .Range("C:C").Find(What:="delete")
            If Not myC Is Nothing Then
                .Range(myC, .Cells(Rows.Count, 3).End(xlUp)).EntireRow.Delete
            End If
            .Cells(1, 3).EntireColumn.Delete
        End With
    End Sub


    HTH, Bernie
    Monday, November 08, 2010 6:50 PM

All replies

  • Based on your description - assumes that columns C of both sheets are otherwise empty:

    Sub Macro1()
        With Sheets("Board")
            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))
            .FormulaR1C1 = _
            "=IF(NOT(ISERROR(MATCH(RC[-1],Test!C[-1],FALSE))),""Yes"","""")"
            .Value = .Value
            End With
        End With
        With Sheets("Test")
            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))
            .FormulaR1C1 = _
            "=IF(ISERROR(MATCH(RC[-1],Board!C[-1],FALSE)),""keep"",""Delete"")"
            .Value = .Value
            End With
            .Range("B1", .Cells(Rows.Count, 3).End(xlUp)).Sort _
                Key1:=.Range("C1"), _
                Order1:=xlDescending, Header:=xlNo
            Set myC = .Range("C:C").Find(What:="delete")
            If Not myC Is Nothing Then
                .Range(myC, .Cells(Rows.Count, 3).End(xlUp)).EntireRow.Delete
            End If
            .Cells(1, 3).EntireColumn.Delete
        End With
    End Sub


    HTH, Bernie
    Monday, November 08, 2010 6:50 PM
  • Thanks Bernie for your help. Yes you are right column C is empty and your codes worked like a charm.

    But one last help, is it possible for you to describe the codes to me. A short description would be great. This would allows me to solve similar problems by myself. Thank you.

    Tuesday, November 09, 2010 1:33 AM
  • My code fills the corresponding parts of column C on each sheet with formulas that return the correct value if like values are found in column B. You can step through the code using F8, and go out to Excel to see the effect. First the formulas on Board are filled, then converted to values, and then the formulas on Test, and then converted to values. Then Test is sorted, and any rows in column C that show delete are deleted, and then column C is removed. The coding syntax is fairly advanced, but the concept is very simple. See comments below:

     

    Sub Macro1()
    'start with Board

        With Sheets("Board")
    'Find the matching range in C

            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))

    'Fill in the formula

            .FormulaR1C1 = _
            "=IF(NOT(ISERROR(MATCH(RC[-1],Test!C[-1],FALSE))),""Yes"","""")"

    'Convert to values

            .Value = .Value
            End With
        End With

    'Do the same for Test
        With Sheets("Test")
            With .Range("C1", .Cells(Rows.Count, 2).End(xlUp)(1, 2))
            .FormulaR1C1 = _
            "=IF(ISERROR(MATCH(RC[-1],Board!C[-1],FALSE)),""keep"",""Delete"")"
            .Value = .Value
            End With

    'Sort Test


            .Range("B1", .Cells(Rows.Count, 3).End(xlUp)).Sort _
                Key1:=.Range("C1"), _
                Order1:=xlDescending, Header:=xlNo

    'Find the first cell with delete, and delete all rows from there down

            Set myC = .Range("C:C").Find(What:="delete")
            If Not myC Is Nothing Then
                .Range(myC, .Cells(Rows.Count, 3).End(xlUp)).EntireRow.Delete
            End If

    'get rid of column C

            .Cells(1, 3).EntireColumn.Delete
        End With
    End Sub



    HTH, Bernie
    Tuesday, November 09, 2010 2:43 AM
  • And you might want to change

    .Cells(1, 3).EntireColumn.Delete

    to

    .Cells(1, 3).EntireColumn.Cells.ClearContents

    if deleting column C will move other columns over.


    HTH, Bernie
    Tuesday, November 09, 2010 2:44 AM