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
|