Compare two cells and pick up the common number..help and thanks.

[NEW2006]

Hi everyone. I am new here. I'm trying to compare two sets of numbers in two cells and then pick the common number(s).

For example:

A1: 123
A2: 345

In this case, the common number is the number 3. So what is the correct formula I need in cell A3 so that it will show the number 3? Sorry, I'm just too newbie.. Thanks for helping.

[AlKey]

Hi NEW2006 and welcome to the forum

What if there is more than one match?

[AlKey]

Copy this formula and paste it in C1 and drag it across to H1 and one row down

=MID($A1,COLUMN(A1),1)

Copy and paste this formula in cell C3 and drag it across to H3

=IFERROR(VALUE(INDEX($C$1:$H$1,MATCH(C2,$C$1:$H$1,0))),"")

	A	B	C	D	E	F	G	H
1	123		1	2	3
2	345		3	4	5
3			3
4

[NEW2006]

Hi NEW2006 and welcome to the forum

What if there is more than one match?

Very good quetion. If there are more than one match then show all them.

Example:
A1: 12345
A2: 34678

In this case 34 are the common numbers, so just show 34 in cell A3. Thanks for the help on this.

[AlKey]

Pleas see attached file.

[XOR LX]

Hi,

Are you always interested in comparing single-digit integers only? So you'll never be looking to see if e.g. 13 occurs in both lists (since this will be effectively two comparions - for 1 and for 3)?

Is there an upper limit on the number of digits that each cell can contain?

Regards

[NEW2006]

Awesome work Alkey. It work! Thanks for the help. I'm very appreciated. Thanks again.

[NEW2006]

Hi,

Are you always interested in comparing single-digit integers only? So you'll never be looking to see if e.g. 13 occurs in both lists (since this will be effectively two comparions - for 1 and for 3)?

Is there an upper limit on the number of digits that each cell can contain?

Regards

No, there should be no limit of digits in each cells. Though 0-9 is all the numbers there is.

[XOR LX]

Ok, I see now that you're happy with the solution offered you by Alkey, so please mark this thread as solved.

Regards

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).