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=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D2=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=K2,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=K2,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(concatenate(C2,D2)=concatenate(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=concatenate(K2,I2),I2,IF(concatenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,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(concatenate(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)=concatenate(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,IF(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=concatenate(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(concatenate(E2,C2)=concatenate(J2,I2),I2,IF(concatenate(E2,C2)=concatenate(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(concatenate(E2,D2)=concatenate(J2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concatenate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))))))))))
> > >
> > > Thank you for your time and toleration.
> > > Luke