5

I have two columns that have numbers in them. The other has the numbers only once, when the other has duplicates. The numbers in these columns don't match. I need to find all the numbers in column B that have a match in column A.

This probably explains it better:
A B
1 2
2 2
4 5
6 5
7 6
8 6

I want to get a result like:
A B C
1 2 1
2 2 1
4 5 0
6 5 0
7 6 1
8 6 1

or any other way for me to identify the cells in column B which don't have a match in column A

I've spent hours googling and trying different solutions but no go, so a nudge to the right direction would be appreciated

Thank you

-M

mmmajuri
  • 63
  • 1
  • 1
  • 4

1 Answers1

10

In cell C1 place:

=COUNTIF(A$1:A$6;B1)

And copy down.

dertkw
  • 7,798
  • 5
  • 37
  • 45
Robert Ilbrink
  • 7,738
  • 2
  • 22
  • 32
  • =COUNTIF(A$1:A$6;B1) did the trick. Openoffice seems to use ; as separator for function attributes. But thank you for your help! I was stuck in a wilderness of LOOKUPS. – mmmajuri Nov 12 '12 at 03:28
  • 1
    The use of , or ; as a separator depends on your country settings. – Robert Ilbrink Nov 12 '12 at 07:55