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

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

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

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

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

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,"")

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

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,"")
> >
> >
> >