Comparing two columns in Excel that are not lined up 2

[laina222]

Hello! Please help!
I have a problem in Excel that I would rather fix with a formula than perform manually. I have two columns, A and B next to each other that have eight digit numbers in them. Column A has about 1000 numbers while Column B has about 700. What I want the formula to do is look at Column A and then Column B and give me a list of the numbers that appear in both columns. The duplicate numbers are not lined up, so the formula can't be like "If column A = column B then yes". Any ideas?

 

[Hasit]

Let me make sure I understand the question:

You are trying to compare 1 number in column B with numbers in column A. If there is a match, then you should be notified somehow of this fact.

If this is correct, I did the following:

Column A had the numbers 1 - 10 (numbers were in order)
Column B had the numbers 3, 9, 2, 1, 11, 12, 13 in that order

I used the following formula in column C which highlighted all those numbers in column B that matched in column A:

=IF(ISNA(MATCH(B1,$A$1:$A$10,0)),"",MATCH(B1,$A$1:$A$10,0))

I have assumed that no numbers repeat.

The only other way I can think of achieving what you need is through VBA, which is where I am afraid my knowledge ceases.

 

[ETID]

Using your above example,....

in col C, paste this formula:
(changing the 3 in $B$3 to match the last row of your data in col B)


=IF(ISERROR(VLOOKUP(A1,$B$1:$B$3,1,FALSE)),"Not in Col B","Is also in col B&quot


Fill down col C

 

[Hasit]

Damn! I forgot about VLOOKUP. Nice one ETID.

 

[SkipVought]

Hi,

If col1 & 2 represent your data then 3 & 4 represent the results.
col 3 (if col 1 data is in col 2 then the Index Number Else #N/A) formula is

=MATCH(A1,$B$1:$B$5,0)

col 4 (if col 2 data is in col 1 then the Index Number Else #N/A) formula is

=MATCH(B1,$A$1:$A$10,0)

1      1      1      1
2      3      #N/A   3
3      7      2      7
4      9      #N/A   9
5      20     #N/A   #N/A   
6             #N/A   
7             3      
8             #N/A   
9             4      
10            #N/A

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[laina222]

Thank you all very much. I've figured it out now.