Comparing rows between sheets and copying cells

[uopint]

So I have two worksheets: W1 and W2.

1. W1 has data in Column A, while W2 has none in that column (except for the header row of course).
2. W1 has rows that W2 does not have, and vice-versa.

I need code that will compare three columns in these two sheets. If the data in these three columns for a particular row match between the two worksheets, then for that row, I need the macro to copy the column A cell in W1 to the corresponding column A cell in W2. Finally, I would like an 'x' be placed into a W1 column if a match was found for that row.

The two sheets do not have the same number of rows, but the data being compared in the three columns should combine to form a unique row match between the two worksheets.

Thanks in advance to anyone who can help.

This is a crosspost:
http://www.ozgrid.com/forum/showthread.php?t=75579
http://www.excelforum.com/showthread.php?t=611582

 

[ravishankar]

HI
paste the following codes in the macro window ( Alt F11)

Sub ccc()
x = Worksheets("W1").Cells(Rows.Count, 2).End(xlUp).Row
y = Worksheets("W2").Cells(Rows.Count, 2).End(xlUp).Row
For a = 2 To x
Cells(x + 1, 2) = "=iserror(match(B" & a & ",w2!B1:B" & y & ",0))"
Cells(x + 1, 3) = "=iserror(match(C" & a & ",w2!C1:C" & y & ",0))"
Cells(x + 1, 4) = "=iserror(match(D" & a & ",w2!D1:D" & y & ",0))"
If Cells(x + 1, 2) = False And Cells(x + 1, 3) = False And Cells(x + 1, 4) = False Then
Worksheets("W2").Cells(a, 1) = Cells(a, 1)
Cells(a, 1) = Cells(a, 1) & " X"
endif
Next a
End Sub

Run the macro. Hopefully it will do what you want since I have not tested it.
Ravi

 

[uopint]

Thanks for the reply Ravi. Unfortunately the code did not work as I needed it to. All it did was copy W1's column A to W2's column A. It didn't check the three columns for a match beforehand.

Let me give you the sheets that I have so you have something more concrete to work with:

Worksheet1:
--A B C D E
--- - - - -
1 g i v e x
2 b y q w
3 w d f g
4 x w r y
5 a n k l


Worksheet2:
--A B C D
--- - - -
1---u o p
2 g i v e
3---j k y


In the above case, only row 1 in W1 and row 2 in W2 would be matched (both their B, C and D columns contain "i" "v" and "e" respectively). In this case then, the data in W1's A1 would be copied to W2's A2. Finally, a x would be placed in W1's E1.

Thanks again for your help!