Comparing columns

[Anerol]

Hi there,

I'm trying to find a way to compare 2 columns that I have. In col A I have 233 numbers and in col B I have about 300 entries. I'm trying to see if any of my numbers in col A match any number in col B. I'm trying to do a simple IF statement... something like if(A1 = B1:B300, true)... but I want it to compare for each entry in col B...

Help?

thanks!

 

[Denial]

Create a temporary third column, consisting of A1 to A233 followed by B1 to B300. Thus this new column occupies (say) C1 to C533. Then sort this column. Now you can scan down the sorted C column, and any duplicates will be adjacent to each other. They can be detected by putting a formula
IF(C2=C1,1,0)
in cell E2, then copying E2 down as far as E533. SUM(E2:E533) will give you the total number of duplications.

Two potential complications:

(1) If you want to know WHERE these dupicates occur rather than merely WHETHER they occur, you will have to create a fourth column D in which you record the original locations in some manner. Then you sort columns C and D together, on the contents of C.

(2) If you have the possibility of duplication appearing in column A alone or in column B alone, then the method will now work without removing those duplicates first. Of course if you adopt approach (1) above, then whilst you will still count such situations you will be able to identify them from the addresses of the original cells.

HTH

 

[Denial]

Oops. Fmuble fnigers. Please replace the "now" in the second last sentence with "not".

 

[onlyadrafter]

Hello,

Try this formula

=IF(ISNA(MATCH(A1,$B$1:$B$9,0)),"NO MATCH","MATCH")

Amend cell refs as required.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[BML]

You could also use the VLOOKUP statement, and use FALSE as the fourth input variable to restrict EXCEL from finding "near matches". For example:

=VLOOKUP(A1,$B$1:$B$300,1,FALSE)

will retrieve the value from column B that got the match. Anything that doesn't match will result in #N/A, which you can then handle with a simple IF...ISNA statement.

Hope this helps!