-
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
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules