How can I find out if numbers in one column are also in a secondcolumn please?

[RobertSE6]

Hello - I hope someone can help please - I'm a bit of a novice it has
to be said !

I have 2 columns of about 800 numbers (varying number of digits
between 6 and 8) and I need to find out how many of the numbers in
column A are also in column B. I just need a total really - don't need
to know which numbers recur

I've been looking at countif and lookup and match and getting very
confused !

It sounds like it should be easy but I'm stumped

Can anyone sort me out?

Robert

 

[T. Valko]

Are there duplicate numbers in the same column?

1...1
4...3
3...1

Depending on which column you compare to the other column you can get
different results.

If you compare the left column to the right column then you get a result of
2. If you compare the right column to the left column then you get a result
of 3. Are both columns the same length?

 

[RobertSE6]

Hi - thanks for the reply - no the numbers are not repeated in the
same column and the columns are of different lengths - I want to know
if the number in for example cell A1 is anywhere in Column B, then
same for A2 etc. But if it makes it easier I only need one final total
- how many in column A appear in column B Hope I'm making sense! If it
makes it any clearer the first column is staff employed a year ago
(their payroll numbers) and column b is the staff in post now

Rob

 

[T. Valko]

Ok, try this...

It's better to compare the shortest column to the longer column. Let's
assume column B is the shorter of the 2 columns.

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B50,A1:A100,0))))

 

[RobertSE6]

Actually its the other way round - Column A is shorter - with 753 rows
and Column B has 827

If I use these numbers in your formula is that going to give me the
correct answer - or do I need to amend the formula?

So I'm using

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B827,A1:A752,0))))

Thanks

Rob

 

[T. Valko]

It'll work either way:

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B827,A1:A752,0))))

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A752,B1:B827,0))))

The idea of comparing the shortest column to the longer column is that there
are less items to "look for". Based on your ranges this won't make any
significant difference.

 

[ryguy7272]

Give this a try:
=SUMPRODUCT(--(COUNTIF(D612,E6:E12)))

Try it on a small sample to verify that it works, and then change your
ranges to match your actual data.

HTH,
Ryan---

 

[RobertSE6]

Thanks very much Ryan - will give that a go aswell

 

[RobertSE6]

Thanks very much for all your help - job done!

Robert

 

[T. Valko]

You're welcome. Thanks for the feedback!