Excel comparision

hi
i really appreciate that u did consider my question ,
My q is if a is a reference data in a sheet 1in any column(say A) and b is a Raw data in sheet 2 in any column(say D),then Raw data must be compared with the reference data of sheet1,and if any match found then that match will be copy to the sheet3 of same workbook..

A(reference data) D(raw data) A(status)
ab xc
bc cb
cd ab match
ef pq
gh cd match
ij zx
kl qr
hj bc MATCH
rh
ch

pls.. help to solve this problem of excel.
thanks in advance.
nitesh

hi
i really appreciate that u did consider my question ,
My q is if a is a reference data in a sheet 1in any column(say A) and b is a Raw data in sheet 2 in any column(say D),then Raw data must be compared with the reference data of sheet1,and if any match found then that match will be copy to the sheet3 of same workbook..


A(reference data) D(raw data) A(status)
ab --------- --------- xc
bc---------- ------ cb
cd ------------- ab match
ef ------------pq
gh --------- cd match
ij ------------ zx
kl------------- qr
hj ------------- bc MATCH
rh
ch

pls.. help to solve this problem of excel.
thanks in advance.
nitesh

Hi NITESH,

Sheet1, column A, Header: Reference data.
Sheet2, column D, Header: Raw data.
Sheet3, column A, Header: Status

If this is ok, then try this code:

Sub Compare()
Dim lRow, lRow2, x As Integer
lRow = Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Sheet2").Range("D2:D" & lRow)
x = 2
Do
If cell.Value = Sheets("Sheet1").Range("A" & x) Then
Sheets("Sheet3").Range("A" & cell.Row).Value = "Match"
End If
x = x + 1
Loop Until x > lRow2
Next cell
End Sub

Does this yield the desired result?

Best regards,
Trowa

thanks for your help! Trowa ,but can you pls.. tell me how to place the above code in a vba editor window and on which sheet i have to put it and save it and how to run it?

Thanks again
Nitesh

sorry to bother u again but when i run this code it run but it is unable to find all match.for example if there is 3 match in raw data of same value then it is shows match only for one .there also some more prob.but this one is main..
thanks again