Cell Value Comparison 2

[RogerBarton]

I have two cells which I want to compare and generate a result in a third cell which shows the common values.

An example, probably best explains what I'm trying to achieve -

A1: 1103056709
A2: 1120456089

The result I want to achieve is:
A3: 1100056009 (ie. a digit by digit comparison, and if the digit is common to both cells, then put it in the answer (A3) cell, otherwise put in a 0)

Is there an easy way to do this?

 

[SkipVought]

Hi,

Maybe Ken Wright or one of the other bight stars of this Forum can conger up a spreadsheet solution.

But in the meantime, you can paste this into a MODULE and use this function like any other spreadsheet function...

Function DigitAnd(r1 As Range, r2 As Range) As String
    Dim i As Integer
    If Len(r1.Value) <> Len(r2.Value) Then Exit Function
    If Not IsNumeric(r1.Value) Then Exit Function
    If Not IsNumeric(r2.Value) Then Exit Function
    DigitAnd = "'"
    For i = 1 To Len(r1.Value)
        If Mid(r1.Value, i, 1) = Mid(r2.Value, i, 1) Then
            DigitAnd = DigitAnd & Mid(r1.Value, i, 1)
        Else
            DigitAnd = DigitAnd & "0"
        End If
    Next
End Function

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[byundt]

RogerBarton,
Here's a worksheet formula that returns your value. It works for 10 digit numbers. Do you need it to be generalized for numbers of any length?
=SUM(IF(MID(A1,ROW(1:10),1)=MID(A2,ROW(1:10),1), MID(A1,ROW(1:10),1)*10^(10-ROW(1:10)),0))

Brad

 

[RogerBarton]

Thanks Skip and Brad! Thanks too to the speed of reply!

Skip - Your solution worked well for me, and produced the desired result!

Brad - I cut & pasted your worksheet formula into my Excel spreadsheet and it did not produce the desired result - it produce an answer of 0. As to your question, both my A1 and A2 numbers are alway 10 digit numbers, and both numbers always begin with a 1.

Regards,
Roger

 

[byundt]

RogerBarton,
I forgot to mention that the preceding formula must be entered as an array formula. Hold the Control and Shift keys down while pressing Enter. Excel should respond by surrounding your formula with curly braces { }. If it doesn't, select the cell, click in the Formula Bar, and Control + Shift + Enter.

Here is the generalized version of the formula, which works for 1 through 15 digits. I wrapped it in a SUMPRODUCT, so it is now a regular formula:
=SUMPRODUCT((MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)=MID(A2,ROW(INDIRECT("1:" & LEN(A1))),1))*(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))*10^(10-ROW(INDIRECT("1:" & LEN(A1)))))

Brad

 

[byundt]

Roger,
The first formula is an array formula, so you need to Control + Shift + Enter. It returns 0 if entered as a regular formula (as you have found).

The second formula is a regular one (no need to Control + Shift + Enter). It also works for any number of digits.

Here is a third formula that works as a regular formula with 10 digits:
=SUMPRODUCT((MID(A1,ROW(1:10),1)=MID(A2,ROW(1:10),1))*(MID(A1,ROW(1:10),1)*10^(10-ROW(1:10))))
Brad

 

[RogerBarton]

Greeting Brad!
Thanks for the additional information .... it just highlights how little I know about Excel!!!

I have done the Control + Shift + Enter, and it has surrounded my formula with curly braces .... but it still produces an answer of 0 (zero).... which is not the desired result.

I have tried your second formula (as a regular formula) and it now results in #REF!

I have tried your third formula (as a regular formula) and it results in #VALUE!

Regards,
Roger



Regards,
Roger

 

[byundt]

Roger,
Here is a sample file showing the three formulas:

http://home.mchsi.com/~byundt/DigitByDigitComparisonTT1066808.xls

Brad

 

[RogerBarton]

Greetings Brad!

Thanks for sending the sample spreadsheet through.

I have seen that your formulas work, and notice a very silly mistake on my behalf as to why your formulas weren't working for me (I had my two comparison values in cells A2 and A3 instead of A1 and A2 .... Oops!!! Funny, how pedantic Excel is! Although not possible, an error message of "You idiot, you've got the values in the wrong cells!" was more appropriate in this case!)

So indeed your formulas do work for me. A big thank you!

I will have to now spend some time dissecting them to truely understand what is going on and increase my Excel knowledge!

Regards,
Roger

 

[byundt]

Roger,
This is not the easiest formula to understand. I've been playing with array formulas for a couple of years now, and it still takes some trial and error to figure out how to build them even when I know at the outset the basic approach required. I only did it because of Skip's challenge.

ROW(1:10) returns the row numbers of rows 1 through 10. A good way to get the series of numbers 1, 2, 3, 4, 5, etc.
LEN(A1) returns the number of characters (digits) in A1
INDIRECT("1:" & LEN(A1)) returns a reference to rows 1 to 10

MID(A1,ROW(1:10),1) returns the characters (digits) in A1, one digit at a time
10^(10-ROW(1:10)) returns the series of numbers 10 billion, 1 billion, 100 million, ..., 10, 1. This series is used to preserve the place of each digit in the overall number.

IF(expression to test, value if True, value if False) returns one of two different values, depending on whether expression to test is either True or False.

SUMPRODUCT is an array formula that is entered like a regular formula. It is good for counting or summing numbers when one or more conditions must be true. In this case, the condition is whether the corresponding digit in the two numbers is the same. If so, add up that digit times a power of 10.

These formulas are comparing the two numbers, one digit at a time. If the digits are the same, then that digit is multiplied by a power of 10. The results are then added up, either by the SUM or SUMPRODUCT function.

Brad