Solved: Compare two columns

HI

Im am trying to compare two columns. Basically to see if Names in column B exist in column A. if yes then indicate in an empty column next to the item

I have tried this code which I got from another site-modified slightly.

[VBA] Sub Compare()
Dim i As Long, n As Long

n = Application.Max(Range("A65536").End(xlUp).Row,
Range("B65536").End(xlUp).Row)

For i = 2 To n
If Range("B" & i).Value = Range("A" & i).Value Then Range("v" & _
i).Value = "Yes"
Next i


End Sub[/VBA]

Its doing for the first name and as soon it comes to another name it doesnt do it.

There are more than 1900 rows. I tried doing a loop but it takes a very long time. Need something quick.
Please can anyone help?
Many Thanks.

Greetings Satyen,

Try:
[vba]
Sub Compare()
Dim n As Long

n = Application.Max(Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)
Range("V2:V" & n) = Evaluate("IF(A2:A" & n & "=B2:B" & n & ",""Yes"","""")")
End Sub
[/vba]

Hope that helps,

Mark

FYI, you are "doing a loop".

I tested it out and it worked for me (Excel 2003). Have you tried stepping through the code to see where (and why) it fails? Have you tried putting Application.ScreenUpdating = False at the top of your code?

Have you considered just putting a formula in column V like this and just filling it down?

=IF(EXACT(A2,B2),"Yes","")

Thanks GTO but it's not worked properly. :( I sorted the data. So it has multiple Johns, Jeffs and Mary's for example one after the other in column B, but when I run the macro it only puts yes's 5 times for the first name John as it appears 5 times in column A. It doesn't matter how many times it appears in column A, JOhn (column B) is in column A then it should put a yes, for all Jeff and Mary included.

jp2112,

=IF(EXACT(A:A,B3),"Yes","")

I tried this it doesnt work.

Sorry about that. I read your code correctly and the part about the speed (or lack thereof) but missed that last bit.

Try:
[vba]
With Range("V2:V" & n)
.Formula = "=IF(COUNTIF($A$2:$A$" & n & ",$B2)>0,""Yes"","""")"
.Value = .Value
End With
[/vba]

Mark

Thank you Mark it has worked, and speedy too!!
Need to mark this as solved now. :-)

I wrote

=IF(EXACT(A2,B2),"Yes","")

not


=IF(EXACT(A:A,B3),"Yes","")

Also note that you need to fill down the formula as far as there are rows of data on your worksheet. But it looks like this isn't what you need.

Hi Satyen,

I am glad that worked :friends:

To mark the thread Solved, look above your first post for the 'Thread Tools' button. There is a 'Marked as Solved' under it only available to the OP.

Mark