2 rows of data, how to se if there is a match

[Question99]

Hi

I have the following problem:

I have to collums A and B and want a formula for collum C. In collum A and B I have a lot of rows with uniqe numbers in them. I need to see if there is to numbers that are the same, and how many numbers are the same?

Exampel:
A: B: C:
Number(1): Number(2): Match:
46 56 0
77 43 0
66 77 1
35 93 0
27 35 1
total:2

I have tried "if" funktions but not with any luck.

Please help me.....thanks!

[zbor]

Try =COUNTIF(A:A, B1)

[Question99]

Thank you zbor, but it dosent work, do you have any other sugestions????

[zbor]

Extend formula down and put SUM at the top

[zbor]

If you want in single cell, without aditional column, you can use this:

=SUMPRODUCT(COUNTIF(A1:A100, B1:B100))

(Although formula =SUMPRODUCT(COUNTIF(A:A, B:B)) will work for whole range this formula might slow your workbook so keep range low as possible as example above..)

[Question99]

I think I did not explain my problem clearly.

A: B: C:
23 17
29 23
19 02
21 44
43 78
02 07

In C1 I want excel to put a "1", if any number i collum A is identical with B1. And then I will take that funktion and fill it down. At the end I will sum C and now I can say that 2 numbers are identical in collum A and B.

Does it make any sence?

And thank you again for helping me.

[zbor]

Will this count 1 or 3?

77 56 0
77 43 0
66 77 1 or 3?
77 93 0
27 35 1

If 3 use =COUNTIF(A:A, B1)
if 1 use =MIN(COUNTIF(A:A, B1), 1)

[Question99]

1

And there is no identical numbers in A

[zbor]

How about that I write above: =MIN(COUNTIF(A:A, B1), 1)

Also.. if you say there is no identical values in A this formula will count you how many values is there and if there's more than I will write you to check A column:

=CHOOSE(1+MIN(2,COUNTIF(A:A, B1)), 0, 1, "Check A column")

[Question99]

Hi zbor

I can not get your funktions to work. Can you try in your own excel and then copy the text into this string? There is to many "spaces" and ","

I want excel to do the following:

=if(A:A=B1;1;"") and then excel types nothing because all the numbers in A is not identical, but it knows that one of the numbers is identical. But I can not get it to type 1, when one number is identical.

Thank you!!!

[zbor]

I just don't know what doesn't work for you??

[Question99]

My problem is I have one collum of numbers, none is identical and there is no system.
Then I have another collum of numbers, nons is identical and there is no system.
I need to find out if there is any numbers from these two collums that is identical.

A:B:C:
3 1 0 (is 1 identical with 3, 4, 7, 5? no then type 0 og nothing)
4 6 0 (is 6 identical with 3, 4, 7, 5? no then type 0 or nothing)
7 2 0 (is 7 identical with 3, 4, 7, 5? no then type 0 or nothing)
5 4 1 (is 3 identical with 3, 4, 7, 5? yes then type 1)

Hope this is clear, and again thank you!

[zbor]

no, I'm sorry, I don't get it...
Can you tell me in example workbook I uploaded what's wrong?

It matching number in B column with all numbers in A column.

But.. If you need also match A column with all numbers in B column, then why in your example:

4 6 0 (is 6 identical with 3, 4, 7, 5? no then type 0 or nothing)

you don't get 1 because number 4 is in last row of B column??

Also,

3 1 0 (is 1 identical with 3, 4, 7, 5? no then type 0 og nothing)
4 6 0 (is 6 identical with 3, 4, 7, 5? no then type 0 or nothing)
7 2 0 (is 7 identical with 3, 4, 7, 5? no then type 0 or nothing)
5 4 1 (is 3 identical with 3, 4, 7, 5? yes then type 1)

Why in first 2 examples you get B1 (1) and B2 (6) comparing with A column, then A3 (7) comparing with A column and telling that 7 is not identical with 3,4,7,5 and in last wondering is (A1) 3 equal with A column and it is?

I really can't figure it out...

[Question99]

Sorry.

After a good nights sleep I can se, that you solved my problem!!!!

Thank you!

This funktion is the solution: =CHOOSE(1+MIN(2;COUNT.IF(A:A;B2));"";1;"check")

If you have the time, can you explain the funktion for me? I think it is nice, when I understand the funktions I use.

And again, thank you very much zbor!

[zbor]

I'm glad that you test it :D

=CHOOSE(1+MIN(2;COUNTIF(A:A;B2));"";1;"check")

Main part of function is: COUNTIF(A:A;B2)
That's function that counts in A:A range how many times B2 appear.

Now, according to your issue it can be:
- none in which case COUNTIF(A:A;B2)=0
- once in which case COUNTIF(A:A;B2)=1
- if there is more than once then it's duplicated value in column A and you need to check it -> COUNTIF(A:A;B2) = 2,3 or more

In last situation... where solution can be 2,3 or more actually when solution is 2 that's already mistake.
So you can put: MIN(2;COUNTIF(A:A;B2))

which will give you:

- if countif is 0 (first case) MIN(2;0) = 0
- if countif is 1 (second case) MIN(2;1) = 1
- if countif is more (third case, a error) MIN(2,anything more than 2) = 2

CHOOSE function select one of up to 254 values based on the index number.

So:

CHOOSE(4; "X";"G";"K";"B";"Z";"A") will return you 4th value (B in this case)

In your case:

=CHOOSE(1+MIN(2;COUNTIF(A:A;B2)); will return you one of the following options:

=CHOOSE(1+{0;1;2}; in other words =CHOOSE(1 or 2 or 3... depends of what is your solution of COUNTIF (above explained)...

and values that can be returned are: "" or 1 or"check" (in that order)

need more explanation?

[teylyn]

I don't see why it needs to be so complicated. If all values in column A are unique and all values in column B are unique, then this in C1, copied down, should do it, like zbor suggested in his first reply.

=COUNTIF($A$1:$A$4,B1)

see attached