| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Simplify formula
Pardon the new thread about the same thing I posted earlier. I would like to
clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#2
|
|||
|
|||
|
Hi!
Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#3
|
|||
|
|||
|
This is hard to keep straight and I realize as I keep trying to prevail, the
responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#4
|
|||
|
|||
|
Ok, we're making progress!
So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"") Will return: F........G........H ............8.........8 1....................5 ......................... 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#5
|
|||
|
|||
|
Not that easy :). I've been working at this off and on for the last couple
of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#6
|
|||
|
|||
|
Not that easy :). I've been working at this off and on for the last couple
of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#7
|
|||
|
|||
|
Thank you Biff that was the ticket formula... and you thought I would change
it again on this post :) I really do appreciate all of you that helped me on this. I thought maybe you guys got bored because I kept changing it on you in the middle of the stream. Thanks for hanging in there!!! I've said it before and I'm saying it again... YOU GUY ARE THE BEST. BTW Vasant, it's apparently all to do with the relevance of C2 to F2, D2 to G2, & E2 to H2 as Biff mentioned. The I,J & K columns are seemingly irrelevent to position in the rows. Strange but the formula works thus far and rest assured that if it fails anywhere in my quest... I'll Be Back :) Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#8
|
|||
|
|||
|
Okay I you a pat on the back and now I think maybe Vasant had a point. Let
me show you the errors that appeared after futher review: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 7 0 4 1 4 4 8 6 2 6 2 2 2 9 4 5 5 4 5 5 4 5 10 3 6 9 6 6 6 11 5 0 1 1 1 12 0 0 5 5 5 13 9 2 4 2 2 2 14 2 6 9 9 9 9 15 1 4 9 1 1 1 This is weird in that why would that formula work for most situations but error on others? I keep reverting back to the very long formula I mentioned before where, in F2, IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I wonder though if we are not considering this: in F2, IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I'll keep chugging... Appologies for saying that it worked before I ran through the master sheet. I've been using an abreviated sheet and only tried the formula there. Thanks again Biff It is closer than I've been able to do and I will try and understand the formula so that I might find a solution. Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#9
|
|||
|
|||
|
Luke,
As you discovered my formula does not work as Vasant pointed out. I obviously didn't test it enough! Just hang in there. "We'll" figure it out! Biff "Luke" wrote in message ... Okay I you a pat on the back and now I think maybe Vasant had a point. Let me show you the errors that appeared after futher review: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 7 0 4 1 4 4 8 6 2 6 2 2 2 9 4 5 5 4 5 5 4 5 10 3 6 9 6 6 6 11 5 0 1 1 1 12 0 0 5 5 5 13 9 2 4 2 2 2 14 2 6 9 9 9 9 15 1 4 9 1 1 1 This is weird in that why would that formula work for most situations but error on others? I keep reverting back to the very long formula I mentioned before where, in F2, IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I wonder though if we are not considering this: in F2, IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I'll keep chugging... Appologies for saying that it worked before I ran through the master sheet. I've been using an abreviated sheet and only tried the formula there. Thanks again Biff It is closer than I've been able to do and I will try and understand the formula so that I might find a solution. Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#10
|
|||
|
|||
|
Thank you guys, I'll check back periodically to this post. Meanwhile I will
also keep on it. Luke "Biff" wrote: Luke, As you discovered my formula does not work as Vasant pointed out. I obviously didn't test it enough! Just hang in there. "We'll" figure it out! Biff "Luke" wrote in message ... Okay I you a pat on the back and now I think maybe Vasant had a point. Let me show you the errors that appeared after futher review: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 7 0 4 1 4 4 8 6 2 6 2 2 2 9 4 5 5 4 5 5 4 5 10 3 6 9 6 6 6 11 5 0 1 1 1 12 0 0 5 5 5 13 9 2 4 2 2 2 14 2 6 9 9 9 9 15 1 4 9 1 1 1 This is weird in that why would that formula work for most situations but error on others? I keep reverting back to the very long formula I mentioned before where, in F2, IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I wonder though if we are not considering this: in F2, IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I'll keep chugging... Appologies for saying that it worked before I ran through the master sheet. I've been using an abreviated sheet and only tried the formula there. Thanks again Biff It is closer than I've been able to do and I will try and understand the formula so that I might find a solution. Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#11
|
|||
|
|||
|
Luke,
Question: If: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5............5....................... .........................................4........ ...5 What results would you expect in F2, G2 and H2? Biff "Luke" wrote in message ... Thank you guys, I'll check back periodically to this post. Meanwhile I will also keep on it. Luke "Biff" wrote: Luke, As you discovered my formula does not work as Vasant pointed out. I obviously didn't test it enough! Just hang in there. "We'll" figure it out! Biff "Luke" wrote in message ... Okay I you a pat on the back and now I think maybe Vasant had a point. Let me show you the errors that appeared after futher review: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 7 0 4 1 4 4 8 6 2 6 2 2 2 9 4 5 5 4 5 5 4 5 10 3 6 9 6 6 6 11 5 0 1 1 1 12 0 0 5 5 5 13 9 2 4 2 2 2 14 2 6 9 9 9 9 15 1 4 9 1 1 1 This is weird in that why would that formula work for most situations but error on others? I keep reverting back to the very long formula I mentioned before where, in F2, IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I wonder though if we are not considering this: in F2, IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I'll keep chugging... Appologies for saying that it worked before I ran through the master sheet. I've been using an abreviated sheet and only tried the formula there. Thanks again Biff It is closer than I've been able to do and I will try and understand the formula so that I might find a solution. Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2 ),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate( C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF( concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I 2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2 ,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E 2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2 ),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate( E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF( concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J 2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,""))))))))) ))))))))))) Thank you for your time and toleration. Luke |
|
#12
|
|||
|
|||
|
Biff,
I want to say that 5 could show in either G2 or H2 as long as only one "5" shows. But then I start thinking relevance and my logic says that: C2 is to F2 & I2 as F2 is to C2, But that starts to be a far stretch when you consider the possibilities. Therefore I think that the latter, 5 shows in either G2 or H2, may be the better way to look at it in terms of formulas. As long as the return is only the 4 and the 5, I don't see where that it would matter either way. C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............. ...........5.......................4..........5 I hope this helps :) Luke "Biff" wrote: Luke, Question: If: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5........................ ....................................4...........5 What results would you expect in F2, G2 and H2? Biff "Luke" wrote in message ... Thank you guys, I'll check back periodically to this post. Meanwhile I will also keep on it. Luke "Biff" wrote: Luke, As you discovered my formula does not work as Vasant pointed out. I obviously didn't test it enough! Just hang in there. "We'll" figure it out! Biff "Luke" wrote in message ... Okay I you a pat on the back and now I think maybe Vasant had a point. Let me show you the errors that appeared after futher review: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 7 0 4 1 4 4 8 6 2 6 2 2 2 9 4 5 5 4 5 5 4 5 10 3 6 9 6 6 6 11 5 0 1 1 1 12 0 0 5 5 5 13 9 2 4 2 2 2 14 2 6 9 9 9 9 15 1 4 9 1 1 1 This is weird in that why would that formula work for most situations but error on others? I keep reverting back to the very long formula I mentioned before where, in F2, IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I wonder though if we are not considering this: in F2, IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2, otherwise "". I'll keep chugging... Appologies for saying that it worked before I ran through the master sheet. I've been using an abreviated sheet and only tried the formula there. Thanks again Biff It is closer than I've been able to do and I will try and understand the formula so that I might find a solution. Luke "Biff" wrote: Not that easy :). I've been working at this off and on for the last couple of days Yeah, but with each new post the desired outcome changes! :( I'll take another look! Biff "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Not that easy :). I've been working at this off and on for the last couple of days. Your formula's result doesn't change even if there is only 1 "8" on the right side in the top row. My understanding is that if there are 2 "8"s on the left side, there have to be 2 on the right as well. It's possible to test for this but it gives rise to a host of other complications. -- Vasant "Biff" wrote in message ... Ok, we're making progress! So, if F2 references C2 and G2 references D2 then H2 must reference E2 In F2 enter this formula and copy across to H2 then down: =IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,"" ) Will return: F........G........H ...........8.........8 1....................5 ........................ 8.........6........... 9.........0.........2 Biff "Luke" wrote in message ... This is hard to keep straight and I realize as I keep trying to prevail, the responses I get have showed me my own errors. I did say that I2 is relevant to F2. I should have said F2 is relevant to C2 so barring any further revelations, keep in mind that it doesn't matter how the digits in columns I,J & K are aranged. Here is a Corrected Table: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 F2 is relevant only to C2 and only those 6 different combinations in I2, J2 & K2. So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By Concatenated pairs I mean: In cell F2; IF concatenate(C2,D2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2 otherwise IF concatenate(C2,E2)= concatenate(I2,J2) or concatenate(I2,K2) or concatenate(J2,I2) or concatenate(J2,K2) or concatenate(K2,I2) or concatenate(K2,J2) then C2,"")) Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no "2" in I2, J2 or K2. Likewise: G2 is relevant only to D2 and only those 6 different combinations in I2, J2 & K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to any given concatenated pair of I2, J2 & K2 then D2 otherwise "". Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would show the content of D2=8 There has to be a way to do this little puzzle. I hope this time I have made sense accurately. If I have articulated this even remotely close to showing what I would like to have happen in F,G & H then I think I have accomplished something in that alone :) but I would like to succeed. Thank you, Luke "Biff" wrote: Hi! Based on your explanation of what you want and comparing that to your table, I can't see this being done! Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2) Why would F5 = 6 (I5) and F2 not = 8 (I2) Biff "Luke" wrote in message ... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. |
|
#13
|
|||
|
|||
|
Im starting out far again since I cant see names any more
try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,"")) and in H2 =(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,"")) A simpler, to me, description of what you want would be If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as I,J,or K2 then make F2 = C2 If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as I,J,or K2 then make G2 = D2 If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as I,J,or K2 then make H2 = E2 If I am wrong please leet me know. "Luke" wrote: Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#14
|
|||
|
|||
|
bj,
It didn't work but don't discourage... The main thing to consider is: in F2 if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". in G2 Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three digits of I2, J2 or K2, then G2, otherwise "". in H2 Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". In the case where two digits are the same in either C2, D2 or E2, the result in F,G & H would look something like this: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............. ...........5.......................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ...................................4..........5 As long as the return is only the one 4 and one 5, I don't see that it would matter if 5 showed up in G or H either way. To clarify, the same thing in different arrangements would look like: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............4...........5..........5............4 ...................................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5.......................5 ...........5...........5......................5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........4..........5............. ...........4...........5......................4 OR if all three digits are on the right C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ..........5...........5..........4..........5 OR if all three digits in C,D,E are the same C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5...........Nothing is returned......................4..........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............5 .......................5..........4...........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............. ...........5...........5..........4...........5 Does that make since? :) Thank you bj I will work with your formula to see if I can help. Luke "bj" wrote: Im starting out far again since I cant see names any more try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,"")) and in H2 =(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,"")) A simpler, to me, description of what you want would be If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as I,J,or K2 then make F2 = C2 If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as I,J,or K2 then make G2 = D2 If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as I,J,or K2 then make H2 = E2 If I am wrong please leet me know. "Luke" wrote: Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#15
|
|||
|
|||
|
from this last example, it appears there is an additional constraint in that
once a cell is used, It cannot be used again, since both G2 and do not equal 5 try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,""))) and in H2 =if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,"")))) This still may not do what you want depending should empty cells be used in concatinations? in other words does ("", 1,3) give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"] If the blank cells are not to be used, It complicates things dramatically. Would it be possible to put an X or some other character in either, but not both, the IJK or the CDE columns for unused cells? if the answer is no, then the equations will probably double or triple in size. and it woul dprobably be worth going to a UDF. "Luke" wrote: bj, It didn't work but don't discourage... The main thing to consider is: in F2 if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". in G2 Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three digits of I2, J2 or K2, then G2, otherwise "". in H2 Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". In the case where two digits are the same in either C2, D2 or E2, the result in F,G & H would look something like this: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............. ...........5.......................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ...................................4..........5 As long as the return is only the one 4 and one 5, I don't see that it would matter if 5 showed up in G or H either way. To clarify, the same thing in different arrangements would look like: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............4...........5..........5............4 ...................................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5.......................5 ...........5...........5......................5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........4..........5............. ...........4...........5......................4 OR if all three digits are on the right C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ..........5...........5..........4..........5 OR if all three digits in C,D,E are the same C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5...........Nothing is returned......................4..........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............5 .......................5..........4...........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............. ...........5...........5..........4...........5 Does that make since? :) Thank you bj I will work with your formula to see if I can help. Luke "bj" wrote: Im starting out far again since I cant see names any more try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,"")) and in H2 =(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,"")) A simpler, to me, description of what you want would be If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as I,J,or K2 then make F2 = C2 If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as I,J,or K2 then make G2 = D2 If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as I,J,or K2 then make H2 = E2 If I am wrong please leet me know. "Luke" wrote: Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#16
|
|||
|
|||
|
Hi!
Those don't work! I'm stuck on a "minor" detail! The logic needed to satisfy one condition causes the logic needed for the next condition to fail! This is much more complicated than it appears! Biff "bj" wrote in message ... from this last example, it appears there is an additional constraint in that once a cell is used, It cannot be used again, since both G2 and do not equal 5 try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,""))) and in H2 =if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,"")))) This still may not do what you want depending should empty cells be used in concatinations? in other words does ("", 1,3) give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"] If the blank cells are not to be used, It complicates things dramatically. Would it be possible to put an X or some other character in either, but not both, the IJK or the CDE columns for unused cells? if the answer is no, then the equations will probably double or triple in size. and it woul dprobably be worth going to a UDF. "Luke" wrote: bj, It didn't work but don't discourage... The main thing to consider is: in F2 if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". in G2 Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three digits of I2, J2 or K2, then G2, otherwise "". in H2 Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". In the case where two digits are the same in either C2, D2 or E2, the result in F,G & H would look something like this: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............. ...........5.......................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ...................................4..........5 As long as the return is only the one 4 and one 5, I don't see that it would matter if 5 showed up in G or H either way. To clarify, the same thing in different arrangements would look like: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............4...........5..........5............4 ...................................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5.......................5 ...........5...........5......................5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........4..........5............. ...........4...........5......................4 OR if all three digits are on the right C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ..........5...........5..........4..........5 OR if all three digits in C,D,E are the same C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5...........Nothing is returned......................4..........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............5 .......................5..........4...........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............. ...........5...........5..........4...........5 Does that make since? :) Thank you bj I will work with your formula to see if I can help. Luke "bj" wrote: Im starting out far again since I cant see names any more try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,"")) and in H2 =(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,"")) A simpler, to me, description of what you want would be If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as I,J,or K2 then make F2 = C2 If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as I,J,or K2 then make G2 = D2 If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as I,J,or K2 then make H2 = E2 If I am wrong please leet me know. "Luke" wrote: Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#17
|
|||
|
|||
|
I may have typed something wrong (I do most of the time).
This worked on the examples I had. Can you show me where the logic fails so that hopefully, I can do a "mea culpa, I meant to type it this way" ? (hopefully it won't be the "Mea culpa, I don't know how to fix it" response) "Biff" wrote: Hi! Those don't work! I'm stuck on a "minor" detail! The logic needed to satisfy one condition causes the logic needed for the next condition to fail! This is much more complicated than it appears! Biff "bj" wrote in message ... from this last example, it appears there is an additional constraint in that once a cell is used, It cannot be used again, since both G2 and do not equal 5 try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,""))) and in H2 =if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,"")))) This still may not do what you want depending should empty cells be used in concatinations? in other words does ("", 1,3) give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"] If the blank cells are not to be used, It complicates things dramatically. Would it be possible to put an X or some other character in either, but not both, the IJK or the CDE columns for unused cells? if the answer is no, then the equations will probably double or triple in size. and it woul dprobably be worth going to a UDF. "Luke" wrote: bj, It didn't work but don't discourage... The main thing to consider is: in F2 if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". in G2 Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three digits of I2, J2 or K2, then G2, otherwise "". in H2 Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits of I2, J2 or K2, then F2, otherwise "". In the case where two digits are the same in either C2, D2 or E2, the result in F,G & H would look something like this: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............. ...........5.......................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ...................................4..........5 As long as the return is only the one 4 and one 5, I don't see that it would matter if 5 showed up in G or H either way. To clarify, the same thing in different arrangements would look like: C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............4...........5..........5............4 ...................................4..........5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5.......................5 ...........5...........5......................5 OR C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........4..........5............. ...........4...........5......................4 OR if all three digits are on the right C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 4............5...........5..........4............5 ..........5...........5..........4..........5 OR if all three digits in C,D,E are the same C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5...........Nothing is returned......................4..........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............5 .......................5..........4...........5 Or C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2 5............5...........5..........5............. ...........5...........5..........4...........5 Does that make since? :) Thank you bj I will work with your formula to see if I can help. Luke "bj" wrote: Im starting out far again since I cant see names any more try in F2 =(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,"")) and in G2 =(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,"")) and in H2 =(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,"")) A simpler, to me, description of what you want would be If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as I,J,or K2 then make F2 = C2 If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as I,J,or K2 then make G2 = D2 If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as I,J,or K2 then make H2 = E2 If I am wrong please leet me know. "Luke" wrote: Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 6 9 0 2 2 9 0 2 9 0 I need a more simple formula that will perform the following task. The following formula Below, is referencing Column F in that if any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of I in F. The same formula would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of J in G. The only thing in the formula that chages is that instead of showing I in F, it would show J in G The same would go for G in that any combination of two or more digits from C,D & E that are equal to two or more digits from I,J & K then display contents of K in H. The only thing in the formula that chages is that instead of showing I or J in H, it would show K in H Here is my version of the Formula for column F only... the same formula could be pasted in G & H columns repectively-- If it were in simpler format. =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) ))))))) Thank you for your time and toleration. Luke |
|
#18
|
|||
|
|||
|
Luke wrote...
Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: .... Supplementing your original example with the additional examples in rows 7 to 15 you provided in a follow-up, it looks like C2:E15 contains 2 | 8 | 8 1 | 0 | 5 8 | 3 | 4 8 | 6 | 7 9 | 0 | 2 0 | 4 | 1 6 | 2 | 6 4 | 5 | 5 3 | 6 | 9 5 | 0 | 1 0 | 0 | 5 9 | 2 | 4 2 | 6 | 9 1 | 4 | 9 and I2:K15 contains 8 | _ | 8 9 | 5 | 1 5 | 2 | 4 6 | 2 | 8 2 | 9 | _ _ | _ | 4 2 | _ | 2 _ | 4 | 5 6 | _ | 6 1 | _ | _ 5 | _ | _ 2 | 2 | _ _ | 9 | 9 1 | 1 | _ where underscores represent blank cells. If so, and given your specifications are refiend in your follow-up messages, try the following formula in cell F2. F2: =IF(ABS(COUNTIF($I2:$K2,C2)/ SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"") Fill F2 right into G2:H2, then select F2:H2 and fill down into F3:H15. This results in the following in F2:H15. _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | 5 _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ again with underscores representing blanks. |
|
#19
|
|||
|
|||
|
Harlan does it again <yawn.
Seriously, that's brilliant. And you still say there's nothing original in these NGs? <g (Still trying to figure out how it works ...) Regards, Vasant "Harlan Grove" wrote in message oups.com... Luke wrote... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: ... Supplementing your original example with the additional examples in rows 7 to 15 you provided in a follow-up, it looks like C2:E15 contains 2 | 8 | 8 1 | 0 | 5 8 | 3 | 4 8 | 6 | 7 9 | 0 | 2 0 | 4 | 1 6 | 2 | 6 4 | 5 | 5 3 | 6 | 9 5 | 0 | 1 0 | 0 | 5 9 | 2 | 4 2 | 6 | 9 1 | 4 | 9 and I2:K15 contains 8 | _ | 8 9 | 5 | 1 5 | 2 | 4 6 | 2 | 8 2 | 9 | _ _ | _ | 4 2 | _ | 2 _ | 4 | 5 6 | _ | 6 1 | _ | _ 5 | _ | _ 2 | 2 | _ _ | 9 | 9 1 | 1 | _ where underscores represent blank cells. If so, and given your specifications are refiend in your follow-up messages, try the following formula in cell F2. F2: =IF(ABS(COUNTIF($I2:$K2,C2)/ SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"") Fill F2 right into G2:H2, then select F2:H2 and fill down into F3:H15. This results in the following in F2:H15. _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | 5 _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ again with underscores representing blanks. |
|
#20
|
|||
|
|||
|
Vasant Nanavati wrote...
.... . . . And you still say there's nothing original in these NGs? .... Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing whether x falls between m-w/2 and m+w/2 without having to calculate m twice, and the ratio term is akin to the standard way of counting distinct entries in a range containing duplicates. The gist is that each of C, D or E must account for some but not all of the total count of all C:E in I:K. |
|
#21
|
|||
|
|||
|
That's a lot more efficient than what I came up with and I got the same
results. However, according to the OP, the correct output should be: _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. Biff "Harlan Grove" wrote in message oups.com... Vasant Nanavati wrote... ... . . . And you still say there's nothing original in these NGs? ... Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing whether x falls between m-w/2 and m+w/2 without having to calculate m twice, and the ratio term is akin to the standard way of counting distinct entries in a range containing duplicates. The gist is that each of C, D or E must account for some but not all of the total count of all C:E in I:K. |
|
#22
|
|||
|
|||
|
Stirred things up a bit it appears.
A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. Just a thought here, but perhaps a way out of a good but kickin when it comes to 455 444 senarios :) Luke "Biff" wrote: That's a lot more efficient than what I came up with and I got the same results. However, according to the OP, the correct output should be: _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. Biff "Harlan Grove" wrote in message oups.com... Vasant Nanavati wrote... ... . . . And you still say there's nothing original in these NGs? ... Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing whether x falls between m-w/2 and m+w/2 without having to calculate m twice, and the ratio term is akin to the standard way of counting distinct entries in a range containing duplicates. The gist is that each of C, D or E must account for some but not all of the total count of all C:E in I:K. |
|
#23
|
|||
|
|||
|
If you only knew! <lol
OK, I think I have it but I hope Harlan comes back with a more efficient solution. It's a 2 formula approach. Enter this formula in F2 and copy to G2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))=2,SUMPRODUC T(--(ISNUMBER(MATCH($C2:$E2,$I2:$K2,0))))=2,COUNTIF($ I2:$K2,C2)0),C2,"") Enter this formula in H2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))=2,SUMPRODUC T(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))=2,COUNTIF($I2:$ K2,E2)0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"") Now, select the range F2, G2, H2 and copy down as needed. Biff "Luke" wrote in message ... Stirred things up a bit it appears. A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. Just a thought here, but perhaps a way out of a good but kickin when it comes to 455 444 senarios :) Luke "Biff" wrote: That's a lot more efficient than what I came up with and I got the same results. However, according to the OP, the correct output should be: _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. Biff "Harlan Grove" wrote in message oups.com... Vasant Nanavati wrote... ... . . . And you still say there's nothing original in these NGs? ... Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing whether x falls between m-w/2 and m+w/2 without having to calculate m twice, and the ratio term is akin to the standard way of counting distinct entries in a range containing duplicates. The gist is that each of C, D or E must account for some but not all of the total count of all C:E in I:K. |
|
#24
|
|||
|
|||
|
"Biff" wrote...
.... However, according to the OP, the correct output should be: _ | 8 | 8 .... 4 | 5 | _ .... The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. .... vs _ | 8 | 8 .... 4 | 5 | 5 The records (cols C through K) with my results are 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8 .... 4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5 and the records with the supposed desired results are 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8 .... 4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5 My question is what makes the 8s in D2 and E2 different from the 5s in D9 and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so, F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/ SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ |
|
#25
|
|||
|
|||
|
"Biff" wrote...
.... Enter this formula in F2 and copy to G2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2, $C2:$E2,0))))=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2, $I2:$K2,0))))=2,COUNTIF($I2:$K2,C2)0),C2,"") Enter this formula in H2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2, $C2:$E2,0))))=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2, I2:K2,0))))=2,COUNTIF($I2:$K2,E2)0, COUNT(F2,G2)<COUNT(I2:K2)),E2,"") .... Unfortunately, these formulas would give 5 | 9 | 5 || 5 | 9 | 5 || 5 | 9 | 9 when the results (between the ||s) should presumably be 5 | 9 | _ since there's only one 5 in the I:K cols. |
|
#26
|
|||
|
|||
|
Hi Harlan!
Tried your formula on the OP's sample data and it worked except for the entry: 4 | 5 | 5................... _ | 4 | 5 When I tried it on larger random sets of numbers it didn't fare too well. One thing that happened is when there were no matches in either range the formula returned #DIV/0! Biff "Harlan Grove" wrote in message oups.com... Luke wrote... Pardon the new thread about the same thing I posted earlier. I would like to clean up the mistakes I made in my earlier quest. In it's raw form my setup looks like this: ... Supplementing your original example with the additional examples in rows 7 to 15 you provided in a follow-up, it looks like C2:E15 contains 2 | 8 | 8 1 | 0 | 5 8 | 3 | 4 8 | 6 | 7 9 | 0 | 2 0 | 4 | 1 6 | 2 | 6 4 | 5 | 5 3 | 6 | 9 5 | 0 | 1 0 | 0 | 5 9 | 2 | 4 2 | 6 | 9 1 | 4 | 9 and I2:K15 contains 8 | _ | 8 9 | 5 | 1 5 | 2 | 4 6 | 2 | 8 2 | 9 | _ _ | _ | 4 2 | _ | 2 _ | 4 | 5 6 | _ | 6 1 | _ | _ 5 | _ | _ 2 | 2 | _ _ | 9 | 9 1 | 1 | _ where underscores represent blank cells. If so, and given your specifications are refiend in your follow-up messages, try the following formula in cell F2. F2: =IF(ABS(COUNTIF($I2:$K2,C2)/ SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"") Fill F2 right into G2:H2, then select F2:H2 and fill down into F3:H15. This results in the following in F2:H15. _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | 5 _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ again with underscores representing blanks. |
|
#27
|
|||
|
|||
|
"Biff" wrote...
Tried your formula on the OP's sample data and it worked except for the entry: 4 | 5 | 5................... _ | 4 | 5 Already dealt with in my revised formula. When I tried it on larger random sets of numbers it didn't fare too well. One thing that happened is when there were no matches in either range the formula returned #DIV/0! .... OK, I need to revise my revised formula. F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1, SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") |
|
#28
|
|||
|
|||
|
Biff,
Works 98% with visible exceptions: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 Most work great as above but then, 7 1 1 0 1 1 1 8 0 0 6 0 0 6 0 9 9 9 0 9 9 0 0 9 10 5 5 7 5 5 5 7 11 0 0 7 0 0 0 12 0 8 8 8 8 13 7 5 5 7 5 5 7 5 7 14 7 7 1 7 7 1 7 15 0 0 7 0 0 0 16 8 8 5 8 8 8 5 17 5 5 6 5 5 5 5 18 6 6 0 6 6 6 I'm adding this one because it worked as well: 19 9 9 9 9 9 9 9 Notice the way the doubles are typically showing in G & H and the 755 situation in line 13 (above example)... courious. These are the only type situations that failed through hundreds of samples I ran. This is great! Not sure if this is the response you could live without and I'm not sure if I understand the formula you wrote but it is very very close to done. I would understand if you had enough but if you're into the challenge I'll await your best shot... You guys rock! Luke "Biff" wrote: If you only knew! <lol OK, I think I have it but I hope Harlan comes back with a more efficient solution. It's a 2 formula approach. Enter this formula in F2 and copy to G2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))=2,SUMPRODUC T(--(ISNUMBER(MATCH($C2:$E2,$I2:$K2,0))))=2,COUNTIF($ I2:$K2,C2)0),C2,"") Enter this formula in H2: =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))=2,SUMPRODUC T(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))=2,COUNTIF($I2:$ K2,E2)0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"") Now, select the range F2, G2, H2 and copy down as needed. Biff "Luke" wrote in message ... Stirred things up a bit it appears. A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. Just a thought here, but perhaps a way out of a good but kickin when it comes to 455 444 senarios :) Luke "Biff" wrote: That's a lot more efficient than what I came up with and I got the same results. However, according to the OP, the correct output should be: _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. Biff "Harlan Grove" wrote in message oups.com... Vasant Nanavati wrote... ... . . . And you still say there's nothing original in these NGs? ... Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing whether x falls between m-w/2 and m+w/2 without having to calculate m twice, and the ratio term is akin to the standard way of counting distinct entries in a range containing duplicates. The gist is that each of C, D or E must account for some but not all of the total count of all C:E in I:K. |
|
#29
|
|||
|
|||
|
Harlan Grove,
I tried it and the returns in blank cells I,J,K were #DIV/0!. to clean it up I added: =IF(CONCATENATE($I7279,$J7279,$K7279)="","",IF(AND (ABS(COUNTIF($I7279:$K7279,C7279)/SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5,COUNTIF($C7279:C7279,C7279)<=COUNTIF($I72 79:$K7279,C7279)),C7279,"")) I soon discovered some similar exceptions such as the last post (before this one) I made to Biff. It seems if there are double numbers (ie 988) on the left C,D,E and only one digit on the right in either I,J or K, it wants to return one or two digits, usually in G and/or H. You did eliminate the extra 5 that was appearing so not a total loss. 7 5 5 7 5 x 7 5 7 Perhaps a new function to introduce into excel would be =NOIFANDORBUTIF() lol Luke "Harlan Grove" wrote: "Biff" wrote... .... However, according to the OP, the correct output should be: _ | 8 | 8 .... 4 | 5 | _ .... The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and this is where I'm stuck, the last 5 should not appear in column H. .... vs _ | 8 | 8 .... 4 | 5 | 5 The records (cols C through K) with my results are 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8 .... 4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5 and the records with the supposed desired results are 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8 .... 4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5 My question is what makes the 8s in D2 and E2 different from the 5s in D9 and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so, F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/ SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like _ | 8 | 8 1 | _ | 5 _ | _ | _ 8 | 6 | _ 9 | 0 | 2 _ | _ | _ _ | _ | _ 4 | 5 | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ _ | _ | _ |
|
#30
|
|||
|
|||
|
"Luke" wrote...
Harlan Grove, I tried it and the returns in blank cells I,J,K were #DIV/0!. to clean it up I added: =IF(CONCATENATE($I7279,$J7279,$K7279)="","", IF(AND(ABS(COUNTIF($I7279:$K7279,C7279) /SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5, COUNTIF($C7279:C7279,C7279)<=COUNTIF($I7279:$K727 9,C7279)),C7279,"")) First, no one in their right mind uses the CONCATENATE function. They use the & concatenation operator. Less typing, no wasted nested function call. Second, it's unnecessary. Even if it were necessary to add an outer IF to trap the condition that all cells in I#:K# were blank or empty, it'd make more sense to use =IF(COUNT(I#:K#)=0,"",...) In this case, even that's unnecessary. In another branch of this thread I changed the denominator to MAX(1,SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E72 79))) which eliminates the #DIV/0! problem. I soon discovered some similar exceptions such as the last post (before this one) I made to Biff. It seems if there are double numbers (ie 988) on the left C,D,E and only one digit on the right in either I,J or K, it wants to return one or two digits, usually in G and/or H. .... Yup, more stuff to trap. F2: =IF(AND(COUNT($I2:$K2)1,ABS(COUNTIF($I2:$K2,C2) /MAX(1,SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") |
|
#31
|
|||
|
|||
|
Harlan Grove,
I ran the formula thru hundreds of samples and results are similar to Biff's formula in that when there ore double digits on the left and single digits on the right, results are one digit showing in F,g or h. Such as: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 the above works well, but double digits look like below: 7 3 5 5 5 5 8 8 9 9 9 9 9 2 6 6 6 6 10 1 4 1 1 1 11 5 1 5 5 5 12 6 8 8 8 8 13 3 3 9 3 3 14 8 8 1 8 8 15 0 0 7 0 0 16 2 6 2 2 2 17 9 2 9 9 9 18 4 4 8 4 4 Because there is only one digit on the right in I,J or K, the results seem to follow suit so to speak. Earlier I wrote: A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. I meant to say: in F2, if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2 in F,G or F,H or G,H ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 in F,G or F,H or G,H --- which ever is the easiest to accomplish. Eay for me to say huh... You guys are so close!!! The formula are way beyond me that I am excited to see the final resulting formula. I hope you get paid well. Luke "Harlan Grove" wrote: "Biff" wrote... Tried your formula on the OP's sample data and it worked except for the entry: 4 | 5 | 5................... _ | 4 | 5 Already dealt with in my revised formula. When I tried it on larger random sets of numbers it didn't fare too well. One thing that happened is when there were no matches in either range the formula returned #DIV/0! .... OK, I need to revise my revised formula. F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1, SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") |
|
#32
|
|||
|
|||
|
"Luke" wrote in message
... I hope you get paid well. Not sure if you were serious, but no one gets paid here. We are all volunteers (or masochists!) -- Vasant |
|
#33
|
|||
|
|||
|
Vasant,
You like what you do then... This is good! I volunteer full-time for the Elma, WA Chamber of Commerce and I know your dedication. Thank you for that. Luke "Vasant Nanavati" wrote: "Luke" wrote in message ... I hope you get paid well. Not sure if you were serious, but no one gets paid here. We are all volunteers (or masochists!) -- Vasant |
|
#34
|
|||
|
|||
|
Either you guys are still kicking this around or you don't want to see this
thread reach 40 posts lol :) Okay this isn't exactly the way I thought this would go but accomplishes the goal in a round about way. I added 3 columns moving contents of CDE over to IJK and the contents of IJK over to LMN leaving CDE blank. I then placed this formula in C and filled over to D & C and filled down: =IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2, $K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCAT ENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","" ,IF(CONCATENATE($K2,$J2)="","",J2)))))) In I2, I put in Harlan's Formula and filled over to J & K then filled down: =IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)), C2,"") What my formula did was eliminate single digits that Harlans formula returned. I couldn't do that with Biff's as he had the double digits going on and it was, well, kicking my butt. This avenue is cumbersome but it succeeds. Then, I was going too see if you guys could incorporate my formula into Harlan's. But just as I was about to post this, I decided to try and replaced Harlan's formula with mine. after doing so, I then I got rid of the three columns (LMN) that I added and courious enough, the disired results were revealed and there was the formula I was looking for. Not so cumbersome now. I find it strange that it fell this way. Thanks to you guys... problem solved. I do have Question though. How is the formula I built in relationship with the ones you guys built? I'm going to rate this one "DONE" only because if it weren't for your efforts I might not have seen the light. Thank you deeply for helping me... You guys rock! I'll watch for posts to see if you have an answer to that question. Thanks again Luke "Luke" wrote: Harlan Grove, I ran the formula thru hundreds of samples and results are similar to Biff's formula in that when there ore double digits on the left and single digits on the right, results are one digit showing in F,g or h. Such as: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 the above works well, but double digits look like below: 7 3 5 5 5 5 8 8 9 9 9 9 9 2 6 6 6 6 10 1 4 1 1 1 11 5 1 5 5 5 12 6 8 8 8 8 13 3 3 9 3 3 14 8 8 1 8 8 15 0 0 7 0 0 16 2 6 2 2 2 17 9 2 9 9 9 18 4 4 8 4 4 Because there is only one digit on the right in I,J or K, the results seem to follow suit so to speak. Earlier I wrote: A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. I meant to say: in F2, if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2 in F,G or F,H or G,H ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 in F,G or F,H or G,H --- which ever is the easiest to accomplish. Eay for me to say huh... You guys are so close!!! The formula are way beyond me that I am excited to see the final resulting formula. I hope you get paid well. Luke "Harlan Grove" wrote: "Biff" wrote... Tried your formula on the OP's sample data and it worked except for the entry: 4 | 5 | 5................... _ | 4 | 5 Already dealt with in my revised formula. When I tried it on larger random sets of numbers it didn't fare too well. One thing that happened is when there were no matches in either range the formula returned #DIV/0! .... OK, I need to revise my revised formula. F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1, SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") |
|
#35
|
|||
|
|||
|
"Luke" wrote...
Either you guys are still kicking this around or you don't want to see this thread reach 40 posts lol :) Okay this isn't exactly the way I thought this would go but accomplishes the goal in a round about way. .... You missed one of my earlier responses. See http://groups-beta.google.com/group/...e=source&hl=en (or http://makeashorterlink.com/?B5C51270B ). |
|
#36
|
|||
|
|||
|
P.S., I dub this formula the =NOIFANDORBUTIF() formula :)
Luke "Luke" wrote: Either you guys are still kicking this around or you don't want to see this thread reach 40 posts lol :) Okay this isn't exactly the way I thought this would go but accomplishes the goal in a round about way. I added 3 columns moving contents of CDE over to IJK and the contents of IJK over to LMN leaving CDE blank. I then placed this formula in C and filled over to D & C and filled down: =IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2, $K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCAT ENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","" ,IF(CONCATENATE($K2,$J2)="","",J2)))))) In I2, I put in Harlan's Formula and filled over to J & K then filled down: =IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)), C2,"") What my formula did was eliminate single digits that Harlans formula returned. I couldn't do that with Biff's as he had the double digits going on and it was, well, kicking my butt. This avenue is cumbersome but it succeeds. Then, I was going too see if you guys could incorporate my formula into Harlan's. But just as I was about to post this, I decided to try and replaced Harlan's formula with mine. after doing so, I then I got rid of the three columns (LMN) that I added and courious enough, the disired results were revealed and there was the formula I was looking for. Not so cumbersome now. I find it strange that it fell this way. Thanks to you guys... problem solved. I do have Question though. How is the formula I built in relationship with the ones you guys built? I'm going to rate this one "DONE" only because if it weren't for your efforts I might not have seen the light. Thank you deeply for helping me... You guys rock! I'll watch for posts to see if you have an answer to that question. Thanks again Luke "Luke" wrote: Harlan Grove, I ran the formula thru hundreds of samples and results are similar to Biff's formula in that when there ore double digits on the left and single digits on the right, results are one digit showing in F,g or h. Such as: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 1 5 9 5 1 4 8 3 4 5 2 4 5 8 6 7 8 6 6 2 8 6 9 0 2 9 0 2 2 9 0 the above works well, but double digits look like below: 7 3 5 5 5 5 8 8 9 9 9 9 9 2 6 6 6 6 10 1 4 1 1 1 11 5 1 5 5 5 12 6 8 8 8 8 13 3 3 9 3 3 14 8 8 1 8 8 15 0 0 7 0 0 16 2 6 2 2 2 17 9 2 9 9 9 18 4 4 8 4 4 Because there is only one digit on the right in I,J or K, the results seem to follow suit so to speak. Earlier I wrote: A question arises in that in F2 if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2 Shouldn't it also, in the same sentance say: but only if I2"" or J2"" OR K2"", OTHERWISE display the contents of I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...? Like wise in G2 and H2. I meant to say: in F2, if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2 in F,G or F,H or G,H ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2 in F,G or F,H or G,H --- which ever is the easiest to accomplish. Eay for me to say huh... You guys are so close!!! The formula are way beyond me that I am excited to see the final resulting formula. I hope you get paid well. Luke "Harlan Grove" wrote: "Biff" wrote... Tried your formula on the OP's sample data and it worked except for the entry: 4 | 5 | 5................... _ | 4 | 5 Already dealt with in my revised formula. When I tried it on larger random sets of numbers it didn't fare too well. One thing that happened is when there were no matches in either range the formula returned #DIV/0! .... OK, I need to revise my revised formula. F2: =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1, SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5, COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"") |
|
#37
|
|||
|
|||
|
Harlan! How the heck did I miss that!?
Thank you for pointing that out... Then I dub yours the NOIFANDORBUTIF() formula :) Thanks! Luke "Harlan Grove" wrote: "Luke" wrote... Either you guys are still kicking this around or you don't want to see this thread reach 40 posts lol :) Okay this isn't exactly the way I thought this would go but accomplishes the goal in a round about way. .... You missed one of my earlier responses. See http://groups-beta.google.com/group/...e=source&hl=en (or http://makeashorterlink.com/?B5C51270B ). |
|
#38
|
|||
|
|||
|
"Luke" wrote...
P.S., I dub this formula the =NOIFANDORBUTIF() formula :) .... =IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I 2,$K2)="","", IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2 ,$K2)="","", IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2 ,$J2)="","",J2)))))) .... The only way I2&J2 = "" is if both I2 and J2 are "" or blank. If so, then J2&I2 would also be "". If not, neither would J2&I2 be "". Ditto I2&K2 and J2&K2 and their reversed pairs. So no point to including the reversed pairs. Indeed, you could simplify to =IF(COUNTBLANK($I2:$K2)<=1,J2,"") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula or not? | Excel Worksheet Functions | |||
| put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
| how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| Simplify Linking to several worksheets?? | Excel Worksheet Functions | |||
Linear Mode
