need help ASAP

P

pike188

hey all, I'm having a problem and dont know exactly how to do this...

I have one column of data (2000 lines worth) all are numerical and all
are different, this is column A

in column B I have another list of numerical data, most of this data
will match the data in column A, but not all.

what I'm trying to do is to pull out of column A all the numbers that
have a match in column B,

How do I do this?

thanks

Ryan
 
S

swatsp0p

What do you mean "...pull out of column A all the numbers that have a
match in column B"? Delete them? Highlight them? Put them in another
column? Line them up with the match in 'B'? Something else?

More details please...
 
P

pike188

any of the above except delete them, I need to be able to determine
whitch ones had a match and whitch ones didnt
 
B

BenjieLop

To identify duplicate entries in Column B (as compared to entries i
Column A), you can use this formula:

=IF(COUNTIF($A$1:$A$2000,B1)>0,\"DUPLICATE\",\"\"

Enter the above formula in Cell C1 and copy down.

Now that you have identified the duplicate entries, you can then decid
what to do with them.

Hope this helps you.

Regards
 
M

Morrigan

Assume A1:A2000 are numbers that you are trying to match with B1:B2000
you can try this:

C1 = IF(ISERROR(MATCH(B1,$A$1:$A$2000,0)),"No Match","Match")
C2 = IF(ISERROR(MATCH(B2,$A$1:$A$2000,0)),"No Match","Match")
etc.

Now if you want to view all the numbers in column A that also exist i
column, simply turn on autofilter and filter out all the "No Match".


Hope this helps.
 
Top