Simplify formula

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" <Luke@discussions.microsoft.com> wrote in message
news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
> 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

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" <Luke@discussions.microsoft.com> wrote in message
> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
> > 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
>
>
>

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,COUNTIF($I2:$K2,C2)>0),C2,"")

Will return:

F........G........H
............8.........8
1....................5
.........................
8.........6...........
9.........0.........2

Biff

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
> 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" <Luke@discussions.microsoft.com> wrote in message
>> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
>> > 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
>>
>>
>>

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" <biffinpitt@comcast.net> wrote in message
news:eQ9L2DoTFHA.3952@TK2MSFTNGP15.phx.gbl...
> 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,COUNTIF($I2:$K2,C2)>0),C2,""
)
>
> Will return:
>
> F........G........H
> ...........8.........8
> 1....................5
> ........................
> 8.........6...........
> 9.........0.........2
>
> Biff
>
> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
> > 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" <Luke@discussions.microsoft.com> wrote in message
> >> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
> >> > 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)=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)=concatenate(K2,I2),I2,IF(conc
atenate(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)=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(concatenate(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)=concatenate(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(concatenate(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)=concatenate(K2,J2),I2,IF(conc
atenate(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)=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(concatenate(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)=concatenate(K2,J2),I2,"")))))))))
)))))))))))
> >> >
> >> > Thank you for your time and toleration.
> >> > Luke
> >>
> >>
> >>
>
>

>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
news:uFOI9IoTFHA.548@tk2msftngp13.phx.gbl...
> 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" <biffinpitt@comcast.net> wrote in message
> news:eQ9L2DoTFHA.3952@TK2MSFTNGP15.phx.gbl...
>> 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,COUNTIF($I2:$K2,C2)>0),C2,""
> )
>>
>> Will return:
>>
>> F........G........H
>> ...........8.........8
>> 1....................5
>> ........................
>> 8.........6...........
>> 9.........0.........2
>>
>> Biff
>>
>> "Luke" <Luke@discussions.microsoft.com> wrote in message
>> news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
>> > 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" <Luke@discussions.microsoft.com> wrote in message
>> >> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
>> >> > 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)=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)=concatenate(K2,I2),I2,IF(conc
> atenate(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)=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(concatenate(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)=concatenate(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(concatenate(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)=concatenate(K2,J2),I2,IF(conc
> atenate(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)=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(concatenate(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)=concatenate(K2,J2),I2,"")))))))))
> )))))))))))
>> >> >
>> >> > Thank you for your time and toleration.
>> >> > Luke
>> >>
>> >>
>> >>
>>
>>
>
>

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
> news:uFOI9IoTFHA.548@tk2msftngp13.phx.gbl...
> > 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" <biffinpitt@comcast.net> wrote in message
> > news:eQ9L2DoTFHA.3952@TK2MSFTNGP15.phx.gbl...
> >> 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,COUNTIF($I2:$K2,C2)>0),C2,""
> > )
> >>
> >> Will return:
> >>
> >> F........G........H
> >> ...........8.........8
> >> 1....................5
> >> ........................
> >> 8.........6...........
> >> 9.........0.........2
> >>
> >> Biff
> >>
> >> "Luke" <Luke@discussions.microsoft.com> wrote in message
> >> news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
> >> > 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" <Luke@discussions.microsoft.com> wrote in message
> >> >> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
> >> >> > 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)=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)=concatenate(K2,I2),I2,IF(conc
> > atenate(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)=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(concatenate(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)=concatenate(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(concatenate(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)=concatenate(K2,J2),I2,IF(conc
> > atenate(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)=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(concatenate(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)=concatenate(K2,J2),I2,"")))))))))
> > )))))))))))
> >> >> >
> >> >> > Thank you for your time and toleration.
> >> >> > Luke
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>
>

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
> news:uFOI9IoTFHA.548@tk2msftngp13.phx.gbl...
> > 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" <biffinpitt@comcast.net> wrote in message
> > news:eQ9L2DoTFHA.3952@TK2MSFTNGP15.phx.gbl...
> >> 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,COUNTIF($I2:$K2,C2)>0),C2,""
> > )
> >>
> >> Will return:
> >>
> >> F........G........H
> >> ...........8.........8
> >> 1....................5
> >> ........................
> >> 8.........6...........
> >> 9.........0.........2
> >>
> >> Biff
> >>
> >> "Luke" <Luke@discussions.microsoft.com> wrote in message
> >> news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
> >> > 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" <Luke@discussions.microsoft.com> wrote in message
> >> >> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
> >> >> > 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)=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)=concatenate(K2,I2),I2,IF(conc
> > atenate(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)=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(concatenate(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)=concatenate(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(concatenate(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)=concatenate(K2,J2),I2,IF(conc
> > atenate(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)=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(concatenate(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)=concatenate(K2,J2),I2,"")))))))))
> > )))))))))))
> >> >> >
> >> >> > Thank you for your time and toleration.
> >> >> > Luke
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>
>

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" <Luke@discussions.microsoft.com> wrote in message
news:EC6B3635-9DFE-4A94-9153-4D43928408E9@microsoft.com...
> 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
>> news:uFOI9IoTFHA.548@tk2msftngp13.phx.gbl...
>> > 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" <biffinpitt@comcast.net> wrote in message
>> > news:eQ9L2DoTFHA.3952@TK2MSFTNGP15.phx.gbl...
>> >> 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,COUNTIF($I2:$K2,C2)>0),C2,""
>> > )
>> >>
>> >> Will return:
>> >>
>> >> F........G........H
>> >> ...........8.........8
>> >> 1....................5
>> >> ........................
>> >> 8.........6...........
>> >> 9.........0.........2
>> >>
>> >> Biff
>> >>
>> >> "Luke" <Luke@discussions.microsoft.com> wrote in message
>> >> news:009F4495-B173-4F91-89FC-DA29E8DFCE01@microsoft.com...
>> >> > 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" <Luke@discussions.microsoft.com> wrote in message
>> >> >> news:D5B9242F-935A-49FE-B5F8-FD66A764A33D@microsoft.com...
>> >> >> > 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)=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)=concatenate(K2,I2),I2,IF(conc
>> > atenate(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)=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(concatenate(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)=concatenate(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(concatenate(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)=concatenate(K2,J2),I2,IF(conc
>> > atenate(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)=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(concatenate(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)=concatenate(K2,J2),I2,"")))))))))
>> > )))))))))))
>> >> >> >
>> >> >> > Thank you for your time and toleration.
>> >> >> > Luke
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>>
>>