×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Comparing columns

## Comparing columns

(OP)
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!
Replies continue below

### RE: Comparing columns

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

### RE: Comparing columns

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

### RE: Comparing columns

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!

### RE: Comparing columns

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!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!