Match Columns

  • Thread starter Thread starter Lindsey
  • Start date Start date
L

Lindsey

Hi, I have two columns with numbers. I need to know what numbers in column A
is the same in column B. Could this be done by highlighting? or some other
way? Thanks for your help!
 
Thank you, but the columns are seperate.

A B
12 12
15 16
32 32
543 324

I want know what values that are listed in column B are listed in column A.
Thanks!
 
Insert the following formula in C1 (or whatever row your data in A and
B begins). It will return "In A" if the value in B occurs in A, or
"Not In A" if the value in B doesn't occur in A.

=IF(COUNTIF(A$1:A$5,B1),"In A","Not In A")

Change A$1:A$5 to the range of cells in A that have data.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Thank you, this worked! However I have one last question. Is there a way you
could do this with 3 columns and have it tell you which column it is in and
not in? Thanks!
 
Is there a way you could do this with 3 columns
and have it tell you which column it is in and not in?
Click to expand...

Starts to get a bit longish ...
but here's one play which executes the logic checks & delivers the results

Data is assumed in cols A to C, from row1 down,
where data in col C is to be checked with that in cols A and B

Copy direct from below n then paste directly into D1's formula bar (it's all
meant in the same cell, broken down for easier clarity on the
checks/sequence):

=
IF(C1="","",
IF(AND(COUNTIF(A:A,C1),COUNTIF(B:B,C1)),"In A & B",
IF(AND(COUNTIF(A:A,C1),NOT(COUNTIF(B:B,C1))),"In A not B",
IF(AND(NOT(COUNTIF(A:A,C1)),COUNTIF(B:B,C1)),"In B not A",
"Not in A nor B"))))

With the formula confirmed in D1,
copy D1 down to the last row of data expected in col C

P/s: Do take a moment to press the Yes button below from where you're
reading/posting (ie in MS's newsgroup webpage). Do this rating for all
responses which help to answer your queries
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
 
Back
Top