N
NOYB
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!
Bottom line - what is the easiest way to compare two 3 digit numbers in any
order? meaning 123 compared to 321 is a match since they both have the same
3 digits.
Thanks
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!
Bottom line - what is the easiest way to compare two 3 digit numbers in any
order? meaning 123 compared to 321 is a match since they both have the same
3 digits.
Thanks