PDA

View Full Version : Solved: Compare two columns



satyen
09-08-2009, 01:06 PM
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.

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

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
09-08-2009, 01:26 PM
Greetings Satyen,

Try:

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


Hope that helps,

Mark

JP2112
09-08-2009, 01:31 PM
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
09-08-2009, 01:42 PM
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
09-08-2009, 01:47 PM
jp2112,

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

I tried this it doesnt work.

GTO
09-08-2009, 02:01 PM
Sorry about that. I read your code correctly and the part about the speed (or lack thereof) but missed that last bit.

Try:

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


Mark

satyen
09-08-2009, 02:14 PM
Thank you Mark it has worked, and speedy too!!
Need to mark this as solved now. :-)

JP2112
09-08-2009, 04:12 PM
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
09-08-2009, 06:07 PM
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