How do I record non-equal values?

[dkenebre]

How do I build a formula that would perform the following function?:

C1 to list the values between 0 and 9 that are not equal to the values
in A1 and B1

File Attached: http://www.exceltip.com/forum/attachment.php?postid=302650 (sample sheet.xls)

 

[Jason Morin]

Please do not attach anything. Try this in C1:

=SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,"")

HTH
Jason
Atlanta, GA

-----Original Message-----
How do I build a formula that would perform the following function?:

C1 to list the values between 0 and 9 that are not equal to the values
in A1 and B1

File Attached:

http://www.exceltip.com/forum/attachment.php?postid=302650
(sample sheet.xls)

------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

 

[Michael Bednarek]

How do I build a formula that would perform the following function?:

C1 to list the values between 0 and 9 that are not equal to the values
in A1 and B1

File Attached: http://www.exceltip.com/forum/attachment.php?postid=302650 (sample sheet.xls)

I didn't look at the worksheet, but doesn't this work:
=SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,"")
assuming that A1 and B1 hold numbers between 0 and 9.

However, if A1, B1 contain numbers of any length, you probably need to
code a function in VBA. Try this:
==================================================
Function NotDigits(theArg1 As Range, theArg2 As Range) As String

Dim theCells As String
Dim i As Long

NotDigits = "0123456789"
theCells = theArg1 & theArg2

For i = 1 To Len(theCells)
NotDigits = Replace(NotDigits, Mid(theCells, i, 1), "")
Next i

End Function
==================================================
Invoke this by putting in C1: =NotDigits(A1,B1)

 

[dkenebre]

Michael, I copied and pasted the function into a new module as is., then
placed "=NotDigits(A1,B1)" into C6 after I entered
0 2 3 6 in A6 and 3 5 9 in B6 but only the "=NotDigits(A1,B1)" appeared
as my result in c6.
Also the formula: SUBSTITUTE(SUBSTITUTE("0123456789",A5,""),B5,"")
it only display inself into c5 when I entered the following data
in A1= 0236 and B1=359

 

[Michael Bednarek]

Michael, I copied and pasted the function into a new module as is., then
placed "=NotDigits(A1,B1)" into C6 after I entered
0 2 3 6 in A6 and 3 5 9 in B6 but only the "=NotDigits(A1,B1)" appeared
as my result in c6.

Well, you need to get you cell references right. My cell references
quoted your original post. If you put the the data of interest into
different cells, e.g. A6 and B6 as you write above, then you need to tell
that to NotDigits, as in "=NotDigits(A6,B6)".

Also the formula: SUBSTITUTE(SUBSTITUTE("0123456789",A5,""),B5,"")
it only display inself into c5 when I entered the following data
in A1= 0236 and B1=359

Conversely, if you put the data of interest into cells A1 and B1, you
need to invoke the second function as
"=SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,"").

You should also clarify what the actual numbers are you want to analyse;
why don't you show us the content of your cells A1, B1 (or A6, B6). As I
wrote before, which method you can use depends on the structure of you
numbers.

 

[dkenebre]

Michael, I played around with the function again and it works fine.
Thanks. Problem solved.