Solved: Compare two columns

[satyen]

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.

[GTO]

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

[JP2112]

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","")

[satyen]

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.

[satyen]

jp2112,

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

I tried this it doesnt work.

[GTO]

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

[satyen]

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

[JP2112]

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.

[GTO]

Hi Satyen,

I am glad that worked

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