Excel Formula Help?

  • Thread starter Thread starter NOYB
  • Start date Start date
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
 
How about just adding the cells? sum(123)=6; sum(321)=6 and so on. then
sort by the sum column.

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

If your numbers are in A1 and B1, then try:

=IF(SUM(-ISERROR(FIND(MID(A1,{1,2,3},1),B1)))=0,"Match","No Match")



--ron
 
How about just adding the cells? sum(123)=6; sum(321)=6 and so on. then
sort by the sum column.

Ken
Click to expand...

I presume you mean add the individual numbers in the cells. Maybe I'm missing
something but if that's what you mean, I don't think it will find matches.

123=6
321=6
402=6
222=6
411=6
114=6

etc.


--ron
 
OOPS! I had a senior moment :-(

Ken

Ron Rosenfeld said:
I presume you mean add the individual numbers in the cells. Maybe I'm
missing
something but if that's what you mean, I don't think it will find matches.

123=6
321=6
402=6
222=6
411=6
114=6

etc.


--ron
Click to expand...
 
With the above formula, 113 matches 301.
Try this instead. Arrange your data as shown:
NumA NumB Test
113 301 No Match

vect_m
031
130
310
013
103
301

Insert > Name > Define the following names
NumA
NumB
vect_m
array_p Refers To =(MID(NumB,MID(lista,colt,1),1)*multi)
rowt Refers To ={1;2;3;4;5;6}
colt Refers To ={1,2,3}
lista Refers To ={213;312;132;231;321;123}
multi Refers To ={100,10,1}

Select the six cells below vect_m and enter this array formula with CSE
=SUM(INDEX(array_p,rowt,))
Below Test enter this formula
=IF(COUNTIF(vect_m,NumA)=0,"No Match","Match")
 
If your numbers are in A1 and B1, then try:

=IF(SUM(-ISERROR(FIND(MID(A1,{1,2,3},1),B1)))=0,"Match","No Match")



--ron
Click to expand...

Herbert pointed out an error in my formula. Try this instead:

=IF(SUM(-(LARGE(-MID(A1,{1,2,3},1),{1,2,3})=LARGE(
-MID(B1,{1,2,3},1),{1,2,3})))=-3,"Match","No Match")


--ron
 
Back
Top