Solved: compare columns from two sheets

[rafi_07max]

I would like to compare column A, B & C of sheet 2(testplan) with column B sheet 1(board).

For e.g. if the content in testplan sheet, cell 1 of column C is “RV1”, it should look for “RV1” in the column B of board sheet. “RV1” 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 “RV1” is located in the above mentioned columns of both sheets then,

1) In column C of board sheet, we have to insert “YES” or “NO”. To determine whether to insert “YES” or “NO”, we have to look at the column A of testplan sheet. For e.g. if “RV1” is located at cell C1 in testplan, Then we need to look at cell A1 of testplan.

If cell A1 is an empty cell, then we insert “YES” onto column C of board sheet.

If cell A1 is not empty, then we insert “NO” onto column C of board sheet.

In column D of board sheet, copy and paste the data from column B of testplan,

For e.g. if “RV1” is located at C1 in testplan, Then we need to look at cell B1 of testplan and copy the data in it and paste on the column D of board sheet.

In Testplan Sheet, delete the entire row that contains “RV1”

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

The column C of board sheet is not all blank cells and most of the cells are already filled with contents. So just replace those contents with the comparing.

The column D of board sheet is initially empty

This should be done for the entire column C of tesplan sheet

I have inserted a picture for better illustration

http://www.iimmgg.com/image/2076d4c019bef896be80efee69d782a3

I have attached a sample workbook,
4877

Inside the workbook it contains,
Board sheet and testplan 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
FinalBoard sheet is how the board sheet should look like comparing
FinalTestplan sheet is how the testplan sheet should look like comparing

[mohanvijay]

try this code



Dim dele As String
Dim i, ii, t_lastro, b_lastro As Long

t_lastro = Sheets("testplan").Cells(Rows.Count, 3).End(xlUp).Row
b_lastro = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To t_lastro
t_val = UCase(Trim(Sheets("testplan").Cells(i, 3).Value))
For ii = 1 To b_lastro
b_val = UCase(Trim(Cells(ii, 2).Value))
If t_val = b_val Then
dele = dele & Sheets("testplan").Cells(i, 3).Address & ","
tester = Trim(Sheets("testplan").Cells(i, 1).Value)
If tester = "" Then
Cells(ii, 3).Value = "YES"
Else
Cells(ii, 3).Value = "NO"
End If
Cells(ii, 4).Value = Sheets("testplan").Cells(i, 2).Value
cou = cou + 1
End If
Next ii
Next i

dele = Left(dele, Len(dele) - 1)
Sheets("testplan").Range(dele).EntireRow.Delete

[rafi_07max]

Thanks mohanvijay, your codes worked but when i add more lines to the board and testplan sheet it started to get error.

Is it possible for your codes to run without any error no matter how many lines i iinsert?

i have attached a workbook with your codes and added more lines to my worksheets.
4882

[mohanvijay]

Try this



Dim dele() As Long
Dim i, ii, t_lastro, b_lastro, cou, dcou, x, addd As Long

cou = 1
dcou = 0

t_lastro = Sheets("testplan").Cells(Rows.Count, 3).End(xlUp).Row
ReDim dele(1 To t_lastro) As Long
b_lastro = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To t_lastro
t_val = UCase(Trim(Sheets("testplan").Cells(i, 3).Value))
For ii = 1 To b_lastro
b_val = UCase(Trim(Cells(ii, 2).Value))
If t_val = b_val Then
dele(cou) = i
tester = Trim(Sheets("testplan").Cells(i, 1).Value)
If tester = "" Then
Cells(ii, 3).Value = "YES"
Else
Cells(ii, 3).Value = "NO"
End If
Cells(ii, 4).Value = Sheets("testplan").Cells(i, 2).Value
cou = cou + 1
End If
Next ii
Next i


For x = 1 To cou - 1

If x = 1 Then
addd = dele(x)
Else
addd = dele(x) - dcou
End If
Sheets("testplan").Cells(addd, 2).EntireRow.Delete
dcou = dcou + 1

Next x

[rafi_07max]

Thanks mohanvijay for your time and help. It is working :)

[rafi_07max]

Hi mohanvijay, one last question when i inserted your codes in a button located in the board sheet, the program worked like a charm. But when i insert your codes in a button in another sheet other than 'board' sheet, the program didn't work.
Why is that so? and is it possible to solve this problem?

[mohanvijay]

change all 'cells' in code (not sheets("testplan") ) as sheets("board").cells

this because when you use only 'cells' it takes cells on activesheet

[rafi_07max]

thanks mohanvijay, that worked:)