Solved

# Compare values in 2 columns and populate another column with the results

Posted on 2011-02-16
248 Views
Hello Everyone,

I have an excel spreadsheet that I need to compare two columns of text to find values that match then populate another column with the results.

My first column (A) has 94,000 rows of data.  I have to compare that to my second column (J) that has about 30,000 rows.  For values that match I have to populate another column (B) with a "yes" if there is a match.

Is there a macro that can do this for me?
0
Question by:cyardley
• 5
• 5
• 2

LVL 11

Expert Comment

if(A1 = B1,"Yes","No") - provided that the values to compare are in the same row!

alternative if 2 lists;

if(iserror(vlookup(a1,\$b\$1:\$b\$30000,1,false)),"No","Yes")
0

LVL 11

Expert Comment

the vlookup simply attempts to determine if the value in column A is contained (exactly) within the list in column B
0

LVL 33

Expert Comment

Use this formula on column C if you data is on columns A and B:

=IF(ISNA(VLOOKUP(B1;A:A,1,FALSE)),"","Yes")

jppinto
0

LVL 33

Expert Comment

jppinto
VLOOKUP.xlsx
0

LVL 11

Accepted Solution

Runrigger earned 500 total points
actually, we both read this wrong;

=if(iserror(vlookup(A1,\$J\$1:\$J\$30000,1,false)),"No","Yes")

To re-iterate, this is confirming that the value in column is listed in column J, correct?
0

LVL 11

Expert Comment

To re-iterate, this is confirming that the value in column is listed in column J, correct?

--->>>>> shoudl have been

To re-iterate, this is confirming that the value in column A is listed in column J, correct?
0

Author Closing Comment

Thank You Runrigger!  I should have realized that a vlookup would have worked.
0

LVL 33

Expert Comment

Did you at least looked at my posts??!
0

LVL 11

Expert Comment

jppinto, I believe that your lookup was the wrong way around!
0

LVL 33

Expert Comment

You're right... :) I missed that! LOLOL
0

Author Comment

Hi jppinto,

I did and runrigger is correct it was the wrong way around.  I think I may have caused that confusion with my wording.  I really do appreciate your quick response and value this site and all of the people on here that have helped me.  Again my apologies if there was any confusion.

0

LVL 33

Expert Comment

No problem...I misread the question. Maybe next time... :)
0