Simplify formula

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

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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>>
>>

Thank you guys, I'll check back periodically to this post. Meanwhile I will
also keep on it.
Luke

"Biff" wrote:

> 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Luke,

Question:

If:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5............5................................................................4...........5

What results would you expect in F2, G2 and H2?

Biff

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:41FD64C5-C40E-4A03-A26D-3FD32A1409F8@microsoft.com...
> Thank you guys, I'll check back periodically to this post. Meanwhile I
> will
> also keep on it.
> Luke
>
> "Biff" wrote:
>
>> 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Biff,
I want to say that 5 could show in either G2 or H2 as long as only one "5"
shows. But then I start thinking relevance and my logic says that:
C2 is to F2 & I2 as F2 is to C2, But that starts to be a far stretch when
you consider the possibilities.
Therefore I think that the latter, 5 shows in either G2 or H2, may be the
better way to look at it in terms of formulas. As long as the return is only
the 4 and the 5, I don't see where that it would matter either way.
> C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
> 4............5...........5..........4........................5.......................4..........5
I hope this helps
Luke


"Biff" wrote:

> Luke,
>
> Question:
>
> If:
>
> C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
> 4............5...........5............................................................4...........5
>
> What results would you expect in F2, G2 and H2?
>
> Biff
>
> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:41FD64C5-C40E-4A03-A26D-3FD32A1409F8@microsoft.com...
> > Thank you guys, I'll check back periodically to this post. Meanwhile I
> > will
> > also keep on it.
> > Luke
> >
> > "Biff" wrote:
> >
> >> 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.
> >> >> >> >> >

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

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

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

Hi!

Those don't work!

I'm stuck on a "minor" detail! The logic needed to satisfy one condition
causes the logic needed for the next condition to fail!

This is much more complicated than it appears!

Biff

"bj" <bj@discussions.microsoft.com> wrote in message
news:20ED3FE6-1E56-4D15-BA91-E977111DDA40@microsoft.com...
> 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

I may have typed something wrong (I do most of the time).
This worked on the examples I had. Can you show me where the logic fails so
that hopefully, I can do a "mea culpa, I meant to type it this way" ?
(hopefully it won't be the "Mea culpa, I don't know how to fix it" response)



"Biff" wrote:

> Hi!
>
> Those don't work!
>
> I'm stuck on a "minor" detail! The logic needed to satisfy one condition
> causes the logic needed for the next condition to fail!
>
> This is much more complicated than it appears!
>
> Biff
>
> "bj" <bj@discussions.microsoft.com> wrote in message
> news:20ED3FE6-1E56-4D15-BA91-E977111DDA40@microsoft.com...
> > 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
>
>
>

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:
....

Supplementing your original example with the additional examples in
rows 7 to 15 you provided in a follow-up, it looks like C2:E15
contains

2 | 8 | 8
1 | 0 | 5
8 | 3 | 4
8 | 6 | 7
9 | 0 | 2
0 | 4 | 1
6 | 2 | 6
4 | 5 | 5
3 | 6 | 9
5 | 0 | 1
0 | 0 | 5
9 | 2 | 4
2 | 6 | 9
1 | 4 | 9

and I2:K15 contains

8 | _ | 8
9 | 5 | 1
5 | 2 | 4
6 | 2 | 8
2 | 9 | _
_ | _ | 4
2 | _ | 2
_ | 4 | 5
6 | _ | 6
1 | _ | _
5 | _ | _
2 | 2 | _
_ | 9 | 9
1 | 1 | _

where underscores represent blank cells. If so, and given your
specifications are refiend in your follow-up messages, try the
following formula in cell F2.

F2:
=IF(ABS(COUNTIF($I2:$K2,C2)/
SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"")

Fill F2 right into G2:H2, then select F2:H2 and fill down into
F3:H15. This results in the following in F2:H15.

_ | 8 | 8
1 | _ | 5
_ | _ | _
8 | 6 | _
9 | 0 | 2
_ | _ | _
_ | _ | _
4 | 5 | 5
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _

again with underscores representing blanks.

Harlan does it again <yawn>.

Seriously, that's brilliant. And you still say there's nothing original in
these NGs? <g>

(Still trying to figure out how it works ...)

Regards,

Vasant

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115155194.143783.107820@o13g2000cwo.googlegroups.com...
> 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:
> ...
>
> Supplementing your original example with the additional examples in
> rows 7 to 15 you provided in a follow-up, it looks like C2:E15
> contains
>
> 2 | 8 | 8
> 1 | 0 | 5
> 8 | 3 | 4
> 8 | 6 | 7
> 9 | 0 | 2
> 0 | 4 | 1
> 6 | 2 | 6
> 4 | 5 | 5
> 3 | 6 | 9
> 5 | 0 | 1
> 0 | 0 | 5
> 9 | 2 | 4
> 2 | 6 | 9
> 1 | 4 | 9
>
> and I2:K15 contains
>
> 8 | _ | 8
> 9 | 5 | 1
> 5 | 2 | 4
> 6 | 2 | 8
> 2 | 9 | _
> _ | _ | 4
> 2 | _ | 2
> _ | 4 | 5
> 6 | _ | 6
> 1 | _ | _
> 5 | _ | _
> 2 | 2 | _
> _ | 9 | 9
> 1 | 1 | _
>
> where underscores represent blank cells. If so, and given your
> specifications are refiend in your follow-up messages, try the
> following formula in cell F2.
>
> F2:
> =IF(ABS(COUNTIF($I2:$K2,C2)/
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"")
>
> Fill F2 right into G2:H2, then select F2:H2 and fill down into
> F3:H15. This results in the following in F2:H15.
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | 5
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
> again with underscores representing blanks.
>

Vasant Nanavati wrote...
....
> . . . And you still say there's nothing original in these NGs?
....

Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
whether x falls between m-w/2 and m+w/2 without having to calculate m
twice, and the ratio term is akin to the standard way of counting
distinct entries in a range containing duplicates.

The gist is that each of C, D or E must account for some but not all of
the total count of all C:E in I:K.

That's a lot more efficient than what I came up with and I got the same
results. However, according to the OP, the correct output should be:

_ | 8 | 8
1 | _ | 5
_ | _ | _
8 | 6 | _
9 | 0 | 2
_ | _ | _
_ | _ | _
4 | 5 | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _

The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
this is where I'm stuck, the last 5 should not appear in column H.

Biff

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> Vasant Nanavati wrote...
> ...
>> . . . And you still say there's nothing original in these NGs?
> ...
>
> Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
> whether x falls between m-w/2 and m+w/2 without having to calculate m
> twice, and the ratio term is akin to the standard way of counting
> distinct entries in a range containing duplicates.
>
> The gist is that each of C, D or E must account for some but not all of
> the total count of all C:E in I:K.
>

Stirred things up a bit it appears.
A question arises in that
in F2
if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
Shouldn't it also, in the same sentance say:
but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
Like wise in G2 and H2.
Just a thought here, but perhaps a way out of a good but kickin when it
comes to 455 444 senarios

Luke

"Biff" wrote:

> That's a lot more efficient than what I came up with and I got the same
> results. However, according to the OP, the correct output should be:
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
> this is where I'm stuck, the last 5 should not appear in column H.
>
> Biff
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> > Vasant Nanavati wrote...
> > ...
> >> . . . And you still say there's nothing original in these NGs?
> > ...
> >
> > Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
> > whether x falls between m-w/2 and m+w/2 without having to calculate m
> > twice, and the ratio term is akin to the standard way of counting
> > distinct entries in a range containing duplicates.
> >
> > The gist is that each of C, D or E must account for some but not all of
> > the total count of all C:E in I:K.
> >
>
>
>

If you only knew! <lol>

OK, I think I have it but I hope Harlan comes back with a more efficient
solution.

It's a 2 formula approach.

Enter this formula in F2 and copy to G2:

=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2,$I2:$K2,0))))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")

Enter this formula in H2:

=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"")

Now, select the range F2, G2, H2 and copy down as needed.

Biff

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:2FA0607B-1F27-4B7F-8D69-0B45EE5ACC87@microsoft.com...
> Stirred things up a bit it appears.
> A question arises in that
> in F2
> if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> Shouldn't it also, in the same sentance say:
> but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> Like wise in G2 and H2.
> Just a thought here, but perhaps a way out of a good but kickin when it
> comes to 455 444 senarios
>
> Luke
>
> "Biff" wrote:
>
>> That's a lot more efficient than what I came up with and I got the same
>> results. However, according to the OP, the correct output should be:
>>
>> _ | 8 | 8
>> 1 | _ | 5
>> _ | _ | _
>> 8 | 6 | _
>> 9 | 0 | 2
>> _ | _ | _
>> _ | _ | _
>> 4 | 5 | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>>
>> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally,
>> and
>> this is where I'm stuck, the last 5 should not appear in column H.
>>
>> Biff
>>
>> "Harlan Grove" <hrlngrv@aol.com> wrote in message
>> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
>> > Vasant Nanavati wrote...
>> > ...
>> >> . . . And you still say there's nothing original in these NGs?
>> > ...
>> >
>> > Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
>> > whether x falls between m-w/2 and m+w/2 without having to calculate m
>> > twice, and the ratio term is akin to the standard way of counting
>> > distinct entries in a range containing duplicates.
>> >
>> > The gist is that each of C, D or E must account for some but not all of
>> > the total count of all C:E in I:K.
>> >
>>
>>
>>

=IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2&J2&K2,C2,"",1),D2,"",1),E2,"",1))<2,I2,"")

This approach is as follows:
1. we take string I2&J2&K2 (it is the same as concatenate(I2;J2;K2))
2. then we replace the first appearance of c2 in I2&J2&K2 with empty string ""
3. then we replace the first appearance of d2 in result string with empty string ""
4. then we replace the first appearance of e2 in result string with empty string ""
5. if length of end result is < 2 then it means that at least 2 of C2,D2,E2 appeared in I2,J2,K2

I think it should work.
Let me know if it was any help.

"Biff" <biffinpitt@comcast.net> wrote...
....
>However, according to the OP, the correct output should be:
>
> _ | 8 | 8
....
> 4 | 5 | _
....
>The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
>this is where I'm stuck, the last 5 should not appear in column H.
....

vs

_ | 8 | 8
....
4 | 5 | 5

The records (cols C through K) with my results are

2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
....
4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5

and the records with the supposed desired results are

2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
....
4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5


My question is what makes the 8s in D2 and E2 different from the 5s in D9
and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so,

F2:
=IF(AND(ABS(COUNTIF($I2:$K2,C2)/
SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,
COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")

Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like

_ | 8 | 8
1 | _ | 5
_ | _ | _
8 | 6 | _
9 | 0 | 2
_ | _ | _
_ | _ | _
4 | 5 | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _

"Biff" <biffinpitt@comcast.net> wrote...
....
>Enter this formula in F2 and copy to G2:
>
>=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,
>$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2,
>$I2:$K2,0))))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")
>
>Enter this formula in H2:
>
>=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,
>$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,
>I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,
>COUNT(F2,G2)<COUNT(I2:K2)),E2,"")
....

Unfortunately, these formulas would give

5 | 9 | 5 || 5 | 9 | 5 || 5 | 9 | 9

when the results (between the ||s) should presumably be

5 | 9 | _

since there's only one 5 in the I:K cols.

Hi Harlan!

Tried your formula on the OP's sample data and it worked except for the
entry:

4 | 5 | 5................... _ | 4 | 5

When I tried it on larger random sets of numbers it didn't fare too well.
One thing that happened is when there were no matches in either range the
formula returned #DIV/0!

Biff

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115155194.143783.107820@o13g2000cwo.googlegroups.com...
> 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:
> ...
>
> Supplementing your original example with the additional examples in
> rows 7 to 15 you provided in a follow-up, it looks like C2:E15
> contains
>
> 2 | 8 | 8
> 1 | 0 | 5
> 8 | 3 | 4
> 8 | 6 | 7
> 9 | 0 | 2
> 0 | 4 | 1
> 6 | 2 | 6
> 4 | 5 | 5
> 3 | 6 | 9
> 5 | 0 | 1
> 0 | 0 | 5
> 9 | 2 | 4
> 2 | 6 | 9
> 1 | 4 | 9
>
> and I2:K15 contains
>
> 8 | _ | 8
> 9 | 5 | 1
> 5 | 2 | 4
> 6 | 2 | 8
> 2 | 9 | _
> _ | _ | 4
> 2 | _ | 2
> _ | 4 | 5
> 6 | _ | 6
> 1 | _ | _
> 5 | _ | _
> 2 | 2 | _
> _ | 9 | 9
> 1 | 1 | _
>
> where underscores represent blank cells. If so, and given your
> specifications are refiend in your follow-up messages, try the
> following formula in cell F2.
>
> F2:
> =IF(ABS(COUNTIF($I2:$K2,C2)/
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"")
>
> Fill F2 right into G2:H2, then select F2:H2 and fill down into
> F3:H15. This results in the following in F2:H15.
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | 5
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
> again with underscores representing blanks.
>

"Biff" <biffinpitt@comcast.net> wrote...
>Tried your formula on the OP's sample data and it worked except for the
>entry:
>
>4 | 5 | 5................... _ | 4 | 5

Already dealt with in my revised formula.

>When I tried it on larger random sets of numbers it didn't fare too well.
>One thing that happened is when there were no matches in either range the
>formula returned #DIV/0!
....

OK, I need to revise my revised formula.

F2:
=IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")

Biff,
Works 98% with visible exceptions:
> 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
Most work great as above but then,
> 7 1 1 0 1 1 1
> 8 0 0 6 0 0 6 0
> 9 9 9 0 9 9 0 0 9
> 10 5 5 7 5 5 5 7
> 11 0 0 7 0 0 0
> 12 0 8 8 8 8
> 13 7 5 5 7 5 5 7 5 7
> 14 7 7 1 7 7 1 7
> 15 0 0 7 0 0 0
> 16 8 8 5 8 8 8 5
> 17 5 5 6 5 5 5 5
> 18 6 6 0 6 6 6
I'm adding this one because it worked as well:
> 19 9 9 9 9 9 9 9

Notice the way the doubles are typically showing in G & H and the 755
situation in line 13 (above example)... courious. These are the only type
situations that failed through hundreds of samples I ran.

This is great! Not sure if this is the response you could live without and
I'm not sure if I understand the formula you wrote but it is very very close
to done.
I would understand if you had enough but if you're into the challenge I'll
await your best shot... You guys rock!
Luke


"Biff" wrote:

> If you only knew! <lol>
>
> OK, I think I have it but I hope Harlan comes back with a more efficient
> solution.
>
> It's a 2 formula approach.
>
> Enter this formula in F2 and copy to G2:
>
> =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2,$I2:$K2,0))))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")
>
> Enter this formula in H2:
>
> =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"")
>
> Now, select the range F2, G2, H2 and copy down as needed.
>
> Biff
>
> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:2FA0607B-1F27-4B7F-8D69-0B45EE5ACC87@microsoft.com...
> > Stirred things up a bit it appears.
> > A question arises in that
> > in F2
> > if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> > Shouldn't it also, in the same sentance say:
> > but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> > I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> > Like wise in G2 and H2.
> > Just a thought here, but perhaps a way out of a good but kickin when it
> > comes to 455 444 senarios
> >
> > Luke
> >
> > "Biff" wrote:
> >
> >> That's a lot more efficient than what I came up with and I got the same
> >> results. However, according to the OP, the correct output should be:
> >>
> >> _ | 8 | 8
> >> 1 | _ | 5
> >> _ | _ | _
> >> 8 | 6 | _
> >> 9 | 0 | 2
> >> _ | _ | _
> >> _ | _ | _
> >> 4 | 5 | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >>
> >> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally,
> >> and
> >> this is where I'm stuck, the last 5 should not appear in column H.
> >>
> >> Biff
> >>
> >> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> >> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> >> > Vasant Nanavati wrote...
> >> > ...
> >> >> . . . And you still say there's nothing original in these NGs?
> >> > ...
> >> >
> >> > Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
> >> > whether x falls between m-w/2 and m+w/2 without having to calculate m
> >> > twice, and the ratio term is akin to the standard way of counting
> >> > distinct entries in a range containing duplicates.
> >> >
> >> > The gist is that each of C, D or E must account for some but not all of
> >> > the total count of all C:E in I:K.
> >> >
> >>
> >>
> >>
>
>
>

Harlan Grove,
I tried it and the returns in blank cells I,J,K were #DIV/0!. to clean it
up I added:
=IF(CONCATENATE($I7279,$J7279,$K7279)="","",IF(AND(ABS(COUNTIF($I7279:$K7279,C7279)/SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5,COUNTIF($C7279:C7279,C7279)<=COUNTIF($I7279:$K7279,C7279)),C7279,""))

I soon discovered some similar exceptions such as the last post (before this
one) I made to Biff. It seems if there are double numbers (ie 988) on the
left C,D,E and only one digit on the right in either I,J or K, it wants to
return one or two digits, usually in G and/or H.
You did eliminate the extra 5 that was appearing so not a total loss.
7 5 5 7 5 x 7 5 7
Perhaps a new function to introduce into excel would be =NOIFANDORBUTIF()
lol
Luke


"Harlan Grove" wrote:

> "Biff" <biffinpitt@comcast.net> wrote...
> ....
> >However, according to the OP, the correct output should be:
> >
> > _ | 8 | 8
> ....
> > 4 | 5 | _
> ....
> >The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
> >this is where I'm stuck, the last 5 should not appear in column H.
> ....
>
> vs
>
> _ | 8 | 8
> ....
> 4 | 5 | 5
>
> The records (cols C through K) with my results are
>
> 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
> ....
> 4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5
>
> and the records with the supposed desired results are
>
> 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
> ....
> 4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5
>
>
> My question is what makes the 8s in D2 and E2 different from the 5s in D9
> and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so,
>
> F2:
> =IF(AND(ABS(COUNTIF($I2:$K2,C2)/
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,
> COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
>
> Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
>
>

"Luke" <Luke@discussions.microsoft.com> wrote...
>Harlan Grove,
>I tried it and the returns in blank cells I,J,K were #DIV/0!.
>to clean it up I added:
>=IF(CONCATENATE($I7279,$J7279,$K7279)="","",
>IF(AND(ABS(COUNTIF($I7279:$K7279,C7279)
>/SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5,
>COUNTIF($C7279:C7279,C7279)<=COUNTIF($I7279:$K7279,C7279)),C7279,""))

First, no one in their right mind uses the CONCATENATE function. They use
the & concatenation operator. Less typing, no wasted nested function call.

Second, it's unnecessary. Even if it were necessary to add an outer IF to
trap the condition that all cells in I#:K# were blank or empty, it'd make
more sense to use

=IF(COUNT(I#:K#)=0,"",...)

In this case, even that's unnecessary. In another branch of this thread I
changed the denominator to

MAX(1,SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279)))

which eliminates the #DIV/0! problem.

>I soon discovered some similar exceptions such as the last post
>(before this one) I made to Biff. It seems if there are double
>numbers (ie 988) on the left C,D,E and only one digit on the
>right in either I,J or K, it wants to return one or two digits,
>usually in G and/or H.
....

Yup, more stuff to trap.

F2:
=IF(AND(COUNT($I2:$K2)>1,ABS(COUNTIF($I2:$K2,C2)
/MAX(1,SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")

Harlan Grove,
I ran the formula thru hundreds of samples and results are similar to Biff's
formula in that when there ore double digits on the left and single digits on
the right, results are one digit showing in F,g or h. Such as:
> 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
the above works well, but double digits look like below:
> 7 3 5 5 5 5
> 8 8 9 9 9 9
> 9 2 6 6 6 6
> 10 1 4 1 1 1
> 11 5 1 5 5 5
> 12 6 8 8 8 8
> 13 3 3 9 3 3
> 14 8 8 1 8 8
> 15 0 0 7 0 0
> 16 2 6 2 2 2
> 17 9 2 9 9 9
> 18 4 4 8 4 4
Because there is only one digit on the right in I,J or K, the results seem
to follow suit so to speak.
Earlier I wrote:
A question arises in that
in F2
if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
Shouldn't it also, in the same sentance say:
but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
Like wise in G2 and H2.

I meant to say:
in F2,
if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
in F,G or F,H or G,H
---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
in F,G or F,H or G,H --- which ever is the easiest to accomplish.

Eay for me to say huh... You guys are so close!!! The formula are way beyond
me that I am excited to see the final resulting formula. I hope you get paid
well.
Luke

"Harlan Grove" wrote:

> "Biff" <biffinpitt@comcast.net> wrote...
> >Tried your formula on the OP's sample data and it worked except for the
> >entry:
> >
> >4 | 5 | 5................... _ | 4 | 5
>
> Already dealt with in my revised formula.
>
> >When I tried it on larger random sets of numbers it didn't fare too well.
> >One thing that happened is when there were no matches in either range the
> >formula returned #DIV/0!
> ....
>
> OK, I need to revise my revised formula.
>
> F2:
> =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
>
>
>

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:02A136EA-32C2-4B29-9655-CB894C5FB84F@microsoft.com...
> I hope you get paid well.

Not sure if you were serious, but no one gets paid here. We are all
volunteers (or masochists!)

--

Vasant

Vasant,
You like what you do then... This is good! I volunteer full-time for the
Elma, WA Chamber of Commerce and I know your dedication.
Thank you for that.
Luke

"Vasant Nanavati" wrote:

> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:02A136EA-32C2-4B29-9655-CB894C5FB84F@microsoft.com...
> > I hope you get paid well.
>
> Not sure if you were serious, but no one gets paid here. We are all
> volunteers (or masochists!)
>
> --
>
> Vasant
>
>
>

Either you guys are still kicking this around or you don't want to see this
thread reach 40 posts lol Okay this isn't exactly the way I thought this
would go but accomplishes the goal in a round about way.

I added 3 columns moving contents of CDE over to IJK and the contents of IJK
over to LMN leaving CDE blank.
I then placed this formula in C and filled over to D & C and filled down:

=IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))

In I2, I put in Harlan's Formula and filled over to J & K then filled down:

=IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)),C2,"")

What my formula did was eliminate single digits that Harlans formula
returned. I couldn't do that with Biff's as he had the double digits going
on and it was, well, kicking my butt. This avenue is cumbersome but it
succeeds.

Then, I was going too see if you guys could incorporate my formula into
Harlan's. But just as I was about to post this, I decided to try and
replaced Harlan's formula with mine. after doing so, I then I got rid of the
three columns (LMN) that I added and courious enough, the disired results
were revealed and there was the formula I was looking for. Not so cumbersome
now.

I find it strange that it fell this way. Thanks to you guys... problem
solved.
I do have Question though. How is the formula I built in relationship with
the ones you guys built?
I'm going to rate this one "DONE" only because if it weren't for your
efforts I might not have seen the light. Thank you deeply for helping me...
You guys rock!

I'll watch for posts to see if you have an answer to that question.
Thanks again
Luke



"Luke" wrote:

> Harlan Grove,
> I ran the formula thru hundreds of samples and results are similar to Biff's
> formula in that when there ore double digits on the left and single digits on
> the right, results are one digit showing in F,g or h. Such as:
> > 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
> the above works well, but double digits look like below:
> > 7 3 5 5 5 5
> > 8 8 9 9 9 9
> > 9 2 6 6 6 6
> > 10 1 4 1 1 1
> > 11 5 1 5 5 5
> > 12 6 8 8 8 8
> > 13 3 3 9 3 3
> > 14 8 8 1 8 8
> > 15 0 0 7 0 0
> > 16 2 6 2 2 2
> > 17 9 2 9 9 9
> > 18 4 4 8 4 4
> Because there is only one digit on the right in I,J or K, the results seem
> to follow suit so to speak.
> Earlier I wrote:
> A question arises in that
> in F2
> if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> Shouldn't it also, in the same sentance say:
> but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> Like wise in G2 and H2.
>
> I meant to say:
> in F2,
> if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
> J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
> in F,G or F,H or G,H
> ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> in F,G or F,H or G,H --- which ever is the easiest to accomplish.
>
> Eay for me to say huh... You guys are so close!!! The formula are way beyond
> me that I am excited to see the final resulting formula. I hope you get paid
> well.
> Luke
>
> "Harlan Grove" wrote:
>
> > "Biff" <biffinpitt@comcast.net> wrote...
> > >Tried your formula on the OP's sample data and it worked except for the
> > >entry:
> > >
> > >4 | 5 | 5................... _ | 4 | 5
> >
> > Already dealt with in my revised formula.
> >
> > >When I tried it on larger random sets of numbers it didn't fare too well.
> > >One thing that happened is when there were no matches in either range the
> > >formula returned #DIV/0!
> > ....
> >
> > OK, I need to revise my revised formula.
> >
> > F2:
> > =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> > SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> > COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
> >
> >
> >

"Luke" <Luke@discussions.microsoft.com> wrote...
>Either you guys are still kicking this around or you don't want to see this
>thread reach 40 posts lol Okay this isn't exactly the way I thought
this
>would go but accomplishes the goal in a round about way.
....

You missed one of my earlier responses. See

http://groups-beta.google.com/group/...e=source&hl=en

(or http://makeashorterlink.com/?B5C51270B ).

P.S., I dub this formula the =NOIFANDORBUTIF() formula
Luke

"Luke" wrote:

> Either you guys are still kicking this around or you don't want to see this
> thread reach 40 posts lol Okay this isn't exactly the way I thought this
> would go but accomplishes the goal in a round about way.
>
> I added 3 columns moving contents of CDE over to IJK and the contents of IJK
> over to LMN leaving CDE blank.
> I then placed this formula in C and filled over to D & C and filled down:
>
> =IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))
>
> In I2, I put in Harlan's Formula and filled over to J & K then filled down:
>
> =IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)),C2,"")
>
> What my formula did was eliminate single digits that Harlans formula
> returned. I couldn't do that with Biff's as he had the double digits going
> on and it was, well, kicking my butt. This avenue is cumbersome but it
> succeeds.
>
> Then, I was going too see if you guys could incorporate my formula into
> Harlan's. But just as I was about to post this, I decided to try and
> replaced Harlan's formula with mine. after doing so, I then I got rid of the
> three columns (LMN) that I added and courious enough, the disired results
> were revealed and there was the formula I was looking for. Not so cumbersome
> now.
>
> I find it strange that it fell this way. Thanks to you guys... problem
> solved.
> I do have Question though. How is the formula I built in relationship with
> the ones you guys built?
> I'm going to rate this one "DONE" only because if it weren't for your
> efforts I might not have seen the light. Thank you deeply for helping me...
> You guys rock!
>
> I'll watch for posts to see if you have an answer to that question.
> Thanks again
> Luke
>
>
>
> "Luke" wrote:
>
> > Harlan Grove,
> > I ran the formula thru hundreds of samples and results are similar to Biff's
> > formula in that when there ore double digits on the left and single digits on
> > the right, results are one digit showing in F,g or h. Such as:
> > > 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
> > the above works well, but double digits look like below:
> > > 7 3 5 5 5 5
> > > 8 8 9 9 9 9
> > > 9 2 6 6 6 6
> > > 10 1 4 1 1 1
> > > 11 5 1 5 5 5
> > > 12 6 8 8 8 8
> > > 13 3 3 9 3 3
> > > 14 8 8 1 8 8
> > > 15 0 0 7 0 0
> > > 16 2 6 2 2 2
> > > 17 9 2 9 9 9
> > > 18 4 4 8 4 4
> > Because there is only one digit on the right in I,J or K, the results seem
> > to follow suit so to speak.
> > Earlier I wrote:
> > A question arises in that
> > in F2
> > if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> > Shouldn't it also, in the same sentance say:
> > but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> > I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> > Like wise in G2 and H2.
> >
> > I meant to say:
> > in F2,
> > if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> > and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
> > J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
> > in F,G or F,H or G,H
> > ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> > in F,G or F,H or G,H --- which ever is the easiest to accomplish.
> >
> > Eay for me to say huh... You guys are so close!!! The formula are way beyond
> > me that I am excited to see the final resulting formula. I hope you get paid
> > well.
> > Luke
> >
> > "Harlan Grove" wrote:
> >
> > > "Biff" <biffinpitt@comcast.net> wrote...
> > > >Tried your formula on the OP's sample data and it worked except for the
> > > >entry:
> > > >
> > > >4 | 5 | 5................... _ | 4 | 5
> > >
> > > Already dealt with in my revised formula.
> > >
> > > >When I tried it on larger random sets of numbers it didn't fare too well.
> > > >One thing that happened is when there were no matches in either range the
> > > >formula returned #DIV/0!
> > > ....
> > >
> > > OK, I need to revise my revised formula.
> > >
> > > F2:
> > > =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> > > SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> > > COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
> > >
> > >
> > >

Harlan! How the heck did I miss that!?
Thank you for pointing that out... Then I dub yours the NOIFANDORBUTIF()
formula
Thanks!
Luke

"Harlan Grove" wrote:

> "Luke" <Luke@discussions.microsoft.com> wrote...
> >Either you guys are still kicking this around or you don't want to see this
> >thread reach 40 posts lol Okay this isn't exactly the way I thought
> this
> >would go but accomplishes the goal in a round about way.
> ....
>
> You missed one of my earlier responses. See
>
> http://groups-beta.google.com/group/...e=source&hl=en
>
> (or http://makeashorterlink.com/?B5C51270B ).
>
>
>

"Luke" <Luke@discussions.microsoft.com> wrote...
>P.S., I dub this formula the =NOIFANDORBUTIF() formula
....
>>=IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",
>>IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",
>>IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))
....

The only way I2&J2 = "" is if both I2 and J2 are "" or blank. If so, then
J2&I2 would also be "". If not, neither would J2&I2 be "". Ditto I2&K2 and
J2&K2 and their reversed pairs. So no point to including the reversed pairs.

Indeed, you could simplify to

=IF(COUNTBLANK($I2:$K2)<=1,J2,"")