Answered by:
Compare two column from two different sheets

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
Question
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 Marked as answer by Rafi_excelLover Tuesday, November 09, 2010 1:33 AM
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 Marked as answer by Rafi_excelLover Tuesday, November 09, 2010 1:33 AM

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.

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 BoardWith Sheets("Board")
'Find the matching range in CWith .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 