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
|