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

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?

--
Biff
Microsoft Excel MVP


"RobertSE6" wrote in message
...
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

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

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))))

--
Biff
Microsoft Excel MVP


"RobertSE6" wrote in message
...
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

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

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.


--
Biff
Microsoft Excel MVP


"RobertSE6" wrote in message
...
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

Give this a try:
=SUMPRODUCT(--(COUNTIF(D6:D12,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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"RobertSE6" wrote in message
...
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


.

Thanks very much Ryan - will give that a go aswell

Thanks very much for all your help - job done!

Robert

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RobertSE6" wrote in message
...
Thanks very much for all your help - job done!

Robert