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
|