• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010 formula help please

J

James owens

New Member
I have a formula request...

I need to compare 2 strings of data in 2 different cells and in the third cell i need a formula that tells me how many numbers in both strings of data match.

(Example)

Cell A: 71137
Cell B: 42719
The formula I'm looking for should determine how many numbers from both strings of data match....

Again I need a formula that tells me how many matching numbers are in 2 strings of data, I dont need a formula that gives me a yes/no or true/false calculation, but rather a numerical calculation relative to how many matching numbers are in cells A&B.

Any help with this formula would be greatly appreciated.
 
What do you consider to be a match?

Does position of a number matter? Or just value?
If position is important... there is no match in your example.

And which way would you be comparing?
There's two 7s in A. But one in B. So, what's the expected result in this case?
1, 2 or 3?

It's best to upload sample workbook with how your data is set up and with expected result (generated manually).
 
Chihiro said:
What do you consider to be a match?

Does position of a number matter? Or just value?
If position is important... there is no match in your example.

And which way would you be comparing?
There's two 7s in A. But one in B. So, what's the expected result in this case?
1, 2 or 3?

It's best to upload sample workbook with how your data is set up and with expected result (generated manually).
Click to expand...
Chihiro said:
What do you consider to be a match?

Does position of a number matter? Or just value?
If position is important... there is no match in your example.

And which way would you be comparing?
There's two 7s in A. But one in B. So, what's the expected result in this case?
1, 2 or 3?

It's best to upload sample workbook with how your data is set up and with expected result (generated manually).
Click to expand...



Thanks for you response Chihiro. Position does not matter.... i just need to compare cell A to cell B and determine how many numbers from cell A are in cell B... if cell A has two 7's and cell B has one 7 then the calculation should be... the amount of times it appears doesn't matter, i just want to calcualte how many numbers in both cells match.

Example

Cells:

A. 42766 B. 47615 C. 3 (the 4, 7, 6 match which makes for a calculation of 3 matching numbers)
A. 42765 B. 14909 C. 1 (only the number 1 matches making a calculation of 1)
A. 42764 B. 94713 C. 2 (the 4 and 7 match which gives a calculation of 2)


I just need to figure out how to calculate how many numbers match... again position doesn't matter and the amount of times a number appears in one cell doesn't matter.... I just want to count the ACTUAL numbers that match in both cells. The number is just the number, meaning the number 7 is the number 7 no matter how many times it appears, I just want to calcualte the matching NUMBERS from cells A&B. If cell A has 12345 and cell B has 12225 then the calculation in cell C should be 2 because the numbers 2 and 5 are in both cells.

A. 12345 B. 12225 C. 2
 
Hmm, not so easy with formula. UDF is much easier to do. Are you good with VBA solution?

Also, are numbers always 5 digit?
 
Find and Count of matching number.

1] Find matching number, in D2 CTRL+SHIFT+ENTER copy down :

=IF(COUNT(FIND(0,A2:B2))=2,0,"")&SUBSTITUTE(INT(NPV(-0.9,(MMULT(1-ISERR(FIND(10-ROW($1:$9),A2:B2)),{1;1})=2)*(10-ROW($1:$9)))),0,"")

2] Count of matching number, in E2 copy down :

=LEN(D2)

Regards
Bosco
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top