MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is this possible?????????


Posted by Judy on October 19, 2000 1:51 PM

I would like to compare the items in one column (A) with all the items in another column (G). If an item matches then I would like it to put a "Y" in yet another column (B).


Posted by Christopher Mains on October 19, 2000 2:03 PM

Sub compare()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("G1").Select
j = ActiveCell.Value
If i = j Then
Range("B1").Select
ActiveCell.Value = "y"
Else
If i <> j Then
Range("B1").Select
ActiveCell.Value = "n"
End If
End If
now this is a very simple macro to compare 2 cells and place a value (ie. y and n) in a third cell. Now if you want to hold that first value and move on to the next value in the 2nd column you will have to do a few modification with this macro. You will have to use R1C1 format. i hope this helps

Posted by Ben O. on October 19, 2000 2:03 PM

Sure it's possible. One questions though-- do you want to compare every item in column A with every item in column G? Or do you just want to compare every item in column A with it's corresponding item in column G (i.e. A2 with G2, A3 with G3, etc.)?

If you want to do the former, this is the formula you should use in column B. Just enter the formula in B2 and autofill it as far down as your data goes:

=IF(ISNA(VLOOKUP(A2,G2:G100,1,FALSE)),"","Y")

If you want to do the latter, use this simpler formula:

=IF(A2=G2,"Y","")


-Ben

Posted by JUDY on October 25, 2000 1:12 PM

Thanks, The formula worked!!!!!!!!!!!!!!!!!!!!!!!!

Posted by JUDY on October 25, 2000 1:58 PM


You are right I would have had to modify it to search the whole column. I am not very good at macros so i chickened out. Thanks for taking the time to answer.