# 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 8, 2010 3:29 PM

• 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"), _
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 8, 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"), _
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 8, 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 9, 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()

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"), _

'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 9, 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 9, 2010 2:44 AM