Forum Home
Press F1
Thread ID: 54974	2005-02-27 19:41:00	Yet another XL formula rqd.	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
328704	2005-02-27 19:41:00	I give up, I think senility has well and truly set in! All I want to do is return a 1 if three numbers in column A match three numbers in column B, regardless of order, and 0 if they don’t. Ok: A B 1 3 2 2 3 1 This I want to produce a 1 A B 1 3 2 15 3 1 This I want to produce a 0 Now, quite why I can’t get this to work eludes me, so rather than tear out what little remains of my hair I’ll seek advice. :) Cheers Bob	B.M. (505)
328705	2005-02-27 19:48:00	=if(a1=b1,1,0) is I think what you need first result after the condition a1=b1 is if its true 1 and if false 0 secound result	beama (111)
328706	2005-02-27 20:15:00	Not quite Beama. A1,and A2, and A3, must match B1, and B2, and B3, but in no particular order.	B.M. (505)
328707	2005-02-27 20:18:00	Hi Bob You can do this with a slight variation on the formula from your previous question. But this time there are 6 conditions to test for, rather than just the 2 conditions in your previous question. Using the previous formula as the basis, you could use something like this : =IF( OR( AND( A1=B1,A2=B2,A3=B3), AND(A1=B1,A2=B3,A3=B2), AND(A1=B2,A2=B3,A3=B1), AND(A1=B2,A2=B1,A3=B3), AND(A1=B3,A2=B1,A3=B2), AND(A1=B3,A2=B2,A3=B1 )), 1,0) but that sort of formula will get pretty long-winded if you want to extend your list of numbers much further. If you end up trying to compare great long lists of numbers, then you could try something fancy like this instead : =IF( AND( SUM(A1:A3) = SUM(B1:B3), MIN(A1:A3) = MIN(B1:B3), MAX(A1:A3) = MAX(B1:B3), AVERAGE(A1:A3) = AVERAGE(B1:B3)), 1,0) but there is no guarantee it will work 100% of the time for longer lists of numbers. I'm sure Parry will know a smart function or two to work this out for any length of numbers (no pressure Parry! :D ) HTH, Andrew :)	andrew93 (249)
328708	2005-02-27 22:46:00	vlookup maybe ill have a play later sorry busy at the moment	beama (111)
328709	2005-02-28 00:12:00	Well done again Andrew, that worked a treat. I was working along the same lines but including (“IF’S) which was working fine until I got to the eighth “IF” where I got a big “but” and no “maybe”. :) Beama, I considered trying lookup myself but I think the lookup list has to be in order and in this case that was not guaranteed. Thanks anyway. Bob	B.M. (505)
328710	2005-02-28 00:32:00	Hi, those are some very clever formulas Andrew. :D I dont think this is better but just a different approach: - =AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,$A$1:$A$3,0)+ MATCH(B2,$A$1:$A$3,0)+MATCH(B3,$A$1:$A$3,0))=6) The logic is... Match will return the position within the range, so if all the positions added together equal 6 (1+2+3) then they must be the same. The sum is required because match will return a hit if say the range b1:b3 was 2,2,2 but if they add up to the same amount then they must be the same numbers (if my arithmatic is correct). regards Graham EDIT: btw, this returns True if they are matched or #NA if they are not. This could easily be turned into 1's and 0's if necessary.	Parry (5696)
328711	2005-02-28 01:00:00	Heres with 1's and 0's... =IF(AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,$A$1:$A$3, 0)+ MATCH(B2,$A$1:$A$3,0)+MATCH(B3,$A$1:$A$3,0))=6),1, 0)	Parry (5696)
1