OK I been screwing around with this on and off all day. Trying to write a little Excel formula that will compare two three digit numbers. The catch is it can match in any order as long as both numbers have the same three digits. I started by putting 1 digit in each cell so I can compare each digit of the three digit number, but run into problems.. Imagine a three digit number in cells C1,D1,E1 and then another number to compare it to in C4, D4,E4. I did this little diddy but it fails when the numbers have repeating digits - =If(C4=C1,1,IF(C4=D1,1,IF(C4=E1,1,0))) . This way I assign a value of 1 to a cell if it finds a match. I do this comparision in three different cells and assign each cell a 1 for a match and if my total is 3 then I know I have a match. Again, if there are repeating numbers it will fail like 301 compared to 113 because it will find the one twice and count it. I am sure there is a much simpler way to do this?? Any Excel experts care to comment? Thanks!

I can't help you with your calculation, NOYB, but I have gone into the ms public newsgroups when I need help with something like that and have gotten excellent help.

If your IP supplies you with a newsreader, you can access an Excel newsgroup from this link. It will show up in your mail/news program:
news://msnews.microsoft.com:119/microsoft.public.excel.newusers


or you can access the Excel newsgroups through Google with this link.
http://groups.google.com/groups/dir?sel=33607952

Good luck! Excel drives me nutz when I'm trying to figure out formulas, etc.
_________________


Women and cats will do as they please, and men and dogs should relax and get used to the idea. - Robert Heinlein

Thanks. I didnt think to try news groups. I just guessed at my IP's server and it worked so I am in. Thanks..

I'm no excel guru but looking at the problem from a coding stand point, this is the logic I would use.

Code:

match = true                              ;Assume the munbers match for x= 1 to 3                             ;Outer loop for c1     find = false                          ;Set find flag to false         for t=x to 3                          ;Set inner loop to scan c2           if c1(x) = c2(t) then               ;Does element in c1() = c2()         find = true                       ;If so, set find flag         c2(t)=null                        ;Clear elements in c2 to we don't match again       endif                                      next t                                ; increment t+1         if not(find) then match = false       ; If we didn't find the a match in c2 then numbers can't match                                           ; We could stop here if didn't find match but we should exit loop gracefully                  next x                                    ; increment x+1

If match is true at the end of the nested loop, the numbers would be the same.

The gotcha here is that you need to clear the matching digit when you find it in col2, or you may find it again on the next loop.

333 compared to 321 for example would give a match, unless you cleared the 3 in col2.

Another approach might be to rotate the digits in the 2nd value to all possible combinations and test each against the 1st value. That would work as well.

I'm not sure about the syntax in excel, but can't you use VB type coding to loop and test?
_________________

"Linux is more than an OS, it's a state of mind."

Some examples of things that pass the test and those that fail might help us to understand the problem.

C1,D1,E1 = 1,1,1 C4,D4,E4 = 1,1,1 obvious match

C1,D1,E1 = 1,1,2 C4,D4,E4 = 1,2,1 Match or not?

C1,D1,E1 = 3,2,1 C4,D4,E4 = 1,2,3 Match or not?

C1,D1,E1 = 10,21,33 C4,D4,E4 = 33,10,21 Match or not?

Is the "number" in C1,D1,E1 a three digit number, or is it three one digit numbers? If it is a three digit number, like 113 (three hundred thirteen) or is it three numbers, One, One Three. maybe the numbers in C1, D1, and E1 are not constrained to the range 0-9? so the following wouldn't work.

If it is a three digit number, then it is =(C1*100)+(D1*10)+E1, and the other number would be =(C4*100)+(D4*10)+E4. Then it is trivial to compare those two results.
_________________
RunnerBiker ¿
"Live in such a way that you would not be ashamed to sell your parrot to the town gossip." - Will Rogers

You could sort the columns and simply compare them. That woudn't be as efficient as my 1st solution but it might be easier to accomplish in excel.
_________________

"Linux is more than an OS, it's a state of mind."

Thanks for the tips guys but still no go... I posted on an Excel news group and nobody there has offered any good advice. Just some basic stuff. Things that make you go hmmmm.. there must be an easy way to do this.

I still don't completely understand your problem. Could you answer the questions I asked and elaborate a little more? Provide some examples of things that should match and things that should fail.
_________________
RunnerBiker ¿
"Live in such a way that you would not be ashamed to sell your parrot to the town gossip." - Will Rogers

I want to be able to compare two three digit numbers. A match would be any two three digit numbers that have all the same digits.

matches - 301, 103; 422, 242; 908, 089.
Non-matches would be 301, 133; 422, 442.

My above formula works, but fails on things like 301, 133.

Don't know if there's something here that will help. I've used this guy's stuf a time or 2:
http://www.cpearson.com/excel/topic.htm
_________________


Women and cats will do as they please, and men and dogs should relax and get used to the idea. - Robert Heinlein

NOYB wrote:

I want to be able to compare two three digit numbers. A match would be any two three digit numbers that have all the same digits. matches - 301, 103; 422, 242; 908, 089. Non-matches would be 301, 133; 422, 442. My above formula works, but fails on things like 301, 133.

Thanks for the update. Now I understand the problem. I'll work on a solution. it might take some time...
_________________
RunnerBiker ¿
"Live in such a way that you would not be ashamed to sell your parrot to the town gossip." - Will Rogers

Hi NOYB,

The logic in the first algorithm I posted is sound and does work, it may not be that easy to implement in excel but then again, I've never used the VB scripting portion of excel.

The 2nd suggestion I made works as well.

Just enter the 3 digit numbers in cell A1 & A2 and hit crtl-s to sort. Cell A3 will contain "true" if the numbers match and "false" is they do not.

I split the digits out into B1-B3 and C1-C3 using the following formuals.

1st digit(B1) =TRUNC(A1/100)
2nd digit(B2) =TRUNC(MOD(A1/10,10))
3rd digit(B3) =MOD(A1,10)

I then use a macro to sort the digits into D1-D3 and E1-E3.

Cell A3 contains the comparison =AND(D1=E1,D2=E2,D3=D3)

Here is a .xls using the sort method. (with macro)
noyb-sort.xls

After considering the problem a bit more, there appears to be a simple mathematical solution. I believe if you take each digit and cube it, then sum the cubes, you'll find that the 2 numbers match only if the digits are the same (order doesn't matter). I haven't proved this by brute forcing all the possibilities but I couldn't come up with 2 numbers that failed that test.

If you simply sum the digits, or square the digits and sum, the test will fail but if you cube the digits, the numbers grow to such an extent that only if the digits are the same, will the sums match.

Here is a .xls using the calc method (no macro required)
noyb-calc.xls

You can simplify even more if you combine the funtions which split the digits with the cubes and sums into one formula, but I'll leave that up to you..

Hope this helps!!
_________________

"Linux is more than an OS, it's a state of mind."

Thanks. I knew you would figure it out!

Gald I could help my friend. I had to brush up on excel, being I hadn't used a spreadsheet in a while, but it was fun.

Got anymore?
_________________

"Linux is more than an OS, it's a state of mind."

Good job, GW!
_________________


Women and cats will do as they please, and men and dogs should relax and get used to the idea. - Robert Heinlein