How do I record non-equal values?

M

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)
Click to expand...

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)
 
D

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
 
M

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.
Click to expand...

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
Click to expand...

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.
 
D

dkenebre

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time Difference 1
Workdays issue 3
how can i find a value in a range? 1
Problem with spreadsheet 1
I need to do a calculation based on the values in two cells 2
how to use pivot data in formula or fuctions? 1
Macro help: Adding Specified Blank Formatted Rows Automatically? 4
Standard Error Bars. 1

Top