Finding the missing number across 3 cells

[huat08]

Help needed to find the missing numbers
How can we find the missing number between 0 to 9 across 3 cells (cells A1 to cells C1) and input the missing number in cell E1? any string formula or macro that we can use? both methods are welcome


eg
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64> Cell A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64> Cell B1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64> Cell C1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64> Cell E1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1253</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5973</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3309</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>468</TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

A bit cumbersome but should work.

There may be better ways . . .

=IF(ISERROR(FIND("0",A1&B1&C1)),"0","")
&IF(ISERROR(FIND("1",A1&B1&C1)),"1","")
&IF(ISERROR(FIND("2",A1&B1&C1)),"2","")
&IF(ISERROR(FIND("3",A1&B1&C1)),"3","")
&IF(ISERROR(FIND("4",A1&B1&C1)),"4","")
&IF(ISERROR(FIND("5",A1&B1&C1)),"5","")
&IF(ISERROR(FIND("6",A1&B1&C1)),"6","")
&IF(ISERROR(FIND("7",A1&B1&C1)),"7","")
&IF(ISERROR(FIND("8",A1&B1&C1)),"8","")
&IF(ISERROR(FIND("9",A1&B1&C1)),"9","")

 

[VaraK]

Hi,

One of the possible VBA solutions (I think there could be many solutions to this case):

Function MissingNumFrmRange(r As Range)
Dim cl As Range
Dim t As String, t1 As String, t2 As String
Dim l As Integer, w As Integer, k As Integer
On Error GoTo 0
t = ""
 
For Each cl In r
t = cl.Value
t1 = t1 & t
Next
l = Len(t1)
t = ""
 
For w = 0 To 9
k = 0
    For l = 1 To Len(t1)
        If Mid(t1, l, 1) + 0 = w Then
        k = k + 1
        End If
    Next l
 
    If k = 0 Then
    t2 = w
    t = t & t2
    End If
Next w
MissingNumFrmRange = t
End Function

Paste this into the module.

Formula in your worksheet would be: =MissingNumFrmRange(A1:C1)
Hope this helps.

 

[Ron Coderre]

Try this:

E1: =IF(ISNUMBER(FIND(0,A1&B1&C1)),"",0)&SUBSTITUTE(1234567890-SUMPRODUCT(
ISNUMBER(FIND({9,8,7,6,5,4,3,2,1},A1&B1&C1))
*10^{1,2,3,4,5,6,7,8,9},{9,8,7,6,5,4,3,2,1}),0,"")

 

[Gerald Higgins]

Ron - I don't know about the OP, but I think that looks neat !

 

[huat08]

Thank you all. All your formula works great!

Still have so much to learn to understand your codes. It works great for me!