I'm trying to compare 2 columns of text in Excel to see if the same text appears in both columns or not. So for example I have:

Column A
Apples
Oranges
Pears
Grapes

Column B
Pears
Grapes

I want Excel to show in another column if each is in both, or in just one of the columns, and return some text, like True (if in both columns or False if just in one of the 2 columns)

I'm dealing with 2500 rows of data so need a formula to do it, and that damn paperclip bloke wont give me the answer

I've tried EXACT, IF, OR and AND formulas but cant get them to work

Help appreciated

 

Here's further explanation:
[CODE]

 

THis may not be the most elegant solution but all I can come up with at moment

Firstly make sure that column B is the longer of the 2 lists (if they are not the same size)
Then sort all column A alphabetically

Then for column C, use a formula like

=IF(LOOKUP(B2,$A$2:$A$4)=B2, TRUE, FALSE)

where A2 - A4 are your range of first set of data (make sure you change this range)

this formula is for C2 - simply copy this formula for all results in the C column

Hope it helps - let me know if you need any clarification

 

=vlookup(a2,$B$2:$B$99,1,FALSE)

A2 is the cell you are checking for an occurance of
$B$2:$B$99 is the range which contains the cells you want to search through
1 is the column offset (as your range could be more than one column wide)
FALSE tells it only to return exact matches, otherwise it will return the last value it found before it couldn't find the one it was looking for (relies on the B2:B99 range to be sorted)

Returns N/A if not found

 

Great minds..

 

hmm it wont work

Does it make a difference if the 2 data sets are on different worksheets?

 

Only to your formula

wanna send me the spreadsheet?

 

YHM

 

[QUOTE]Originally posted by AquilaEagle
I'm trying to compare 2 columns of text in Excel to see if the same text appears in both columns or not.

 

Hi Hustn, He got an answer

BTW, there is a flaw in your formula. For it to work the lists would have to be exactly the same (both in content and length). As in AEs example above, if the entries were not on the same line that formula wouldn't spot it.

cat bat false
dog dog true
bat pig false

But it should be true as bat is in the second list.

the VLOOKUP function does this searching of the second list for you, so it doesn't matter where in the list the thing you are looking for is or if they are the same length.