#1   Report Post  
Luke
 
Posts: n/a
Default 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(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke
  #2   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I would like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler
format.

=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke



  #3   Report Post  
Luke
 
Posts: n/a
Default

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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I would like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler
format.


=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke




  #4   Report Post  
Biff
 
Posts: n/a
Default

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

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K
then
display contents of J in G. The only thing in the formula that chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits
from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler
format.


=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke






  #5   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two

or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or

more
digits from C,D & E that are equal to two or more digits from I,J & K
then
display contents of J in G. The only thing in the formula that chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits
from
C,D & E that are equal to two or more digits from I,J & K then

display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same

formula
could be pasted in G & H columns repectively-- If it were in simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke









  #6   Report Post  
Biff
 
Posts: n/a
Default

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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two

or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or

more
digits from C,D & E that are equal to two or more digits from I,J &
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then

display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same

formula
could be pasted in G & H columns repectively-- If it were in simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke









  #7   Report Post  
Luke
 
Posts: n/a
Default

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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two

or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or

more
digits from C,D & E that are equal to two or more digits from I,J &
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then

display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same

formula
could be pasted in G & H columns repectively-- If it were in simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke










  #8   Report Post  
Luke
 
Posts: n/a
Default

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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two

or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or

more
digits from C,D & E that are equal to two or more digits from I,J &
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then

display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same

formula
could be pasted in G & H columns repectively-- If it were in simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke










  #9   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following
task.
The following formula Below, is referencing Column F in that if
any
combination of two or more digits from C,D & E that are equal to
two
or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J
&
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in
simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke












  #10   Report Post  
Luke
 
Posts: n/a
Default

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" wrote in message
...
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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following
task.
The following formula Below, is referencing Column F in that if
any
combination of two or more digits from C,D & E that are equal to
two
or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J
&
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in
simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke















  #11   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
Pardon the new thread about the same thing I posted earlier.
I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following
task.
The following formula Below, is referencing Column F in that
if
any
combination of two or more digits from C,D & E that are equal
to
two
or
more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two
or
more
digits from C,D & E that are equal to two or more digits from
I,J
&
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages
is
that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the
same
formula
could be pasted in G & H columns repectively-- If it were in
simpler
format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=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)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(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(concat enate(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)=concate nate(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(co ncatenate(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)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(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(concat enate(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)=concate nate(K2,J2),I2,"")))))))))
)))))))))))

Thank you for your time and toleration.
Luke















  #12   Report Post  
Luke
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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,CO UNTIF($I2:$K2,C2)0),C2,""
)

Will return:

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

Biff

"Luke" wrote in message
...
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" wrote in message
...
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.

  #13   Report Post  
bj
 
Posts: n/a
Default

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



"Luke" wrote:

Pardon the new thread about the same thing I posted earlier. I would like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler format.


=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke

  #14   Report Post  
Luke
 
Posts: n/a
Default

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the result
in F,G & H would look something like this:

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

OR

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

As long as the return is only the one 4 and one 5, I don't see that it would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............4...........5..........5............4 ...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5.......................5 ...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........4..........5............. ...........4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5..........4............5 ..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............5 .......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............. ...........5...........5..........4...........5
Does that make since? :)
Thank you bj
I will work with your formula to see if I can help.
Luke


"bj" wrote:

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



"Luke" wrote:

Pardon the new thread about the same thing I posted earlier. I would like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler format.



=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke

  #15   Report Post  
bj
 
Posts: n/a
Default

from this last example, it appears there is an additional constraint in that
once a cell is used, It cannot be used again, since both G2 and do not equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("", 1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things dramatically.
Would it be possible to put an X or some other character in either, but not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.


"Luke" wrote:

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the result
in F,G & H would look something like this:

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

OR

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

As long as the return is only the one 4 and one 5, I don't see that it would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............4...........5..........5............4 ...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5.......................5 ...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........4..........5............. ...........4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5..........4............5 ..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............5 .......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............. ...........5...........5..........4...........5
Does that make since? :)
Thank you bj
I will work with your formula to see if I can help.
Luke


"bj" wrote:

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



"Luke" wrote:

Pardon the new thread about the same thing I posted earlier. I would like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler format.




=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke



  #16   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
from this last example, it appears there is an additional constraint in
that
once a cell is used, It cannot be used again, since both G2 and do not
equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("",
1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things
dramatically.
Would it be possible to put an X or some other character in either, but
not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.


"Luke" wrote:

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three
digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the
result
in F,G & H would look something like this:

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

OR

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

As long as the return is only the one 4 and one 5, I don't see that it
would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............4...........5..........5............4 ...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5.......................5 ...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........4..........5............. ...........4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5..........4............5 ..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............5 .......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............. ...........5...........5..........4...........5
Does that make since? :)
Thank you bj
I will work with your formula to see if I can help.
Luke


"bj" wrote:

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the
same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the
same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the
same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



"Luke" wrote:

Pardon the new thread about the same thing I posted earlier. I would
like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two
or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J & K
then
display contents of J in G. The only thing in the formula that chages
is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in simpler
format.




=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke



  #17   Report Post  
bj
 
Posts: n/a
Default

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" wrote in message
...
from this last example, it appears there is an additional constraint in
that
once a cell is used, It cannot be used again, since both G2 and do not
equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D 2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2 ,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and( or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR (E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2= J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("",
1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things
dramatically.
Would it be possible to put an X or some other character in either, but
not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.


"Luke" wrote:

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three
digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the
result
in F,G & H would look something like this:

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

OR

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

As long as the return is only the one 4 and one 5, I don't see that it
would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............4...........5..........5............4 ...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5.......................5 ...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........4..........5............. ...........4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5...........5..........4............5 ..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............5 .......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
5............5...........5..........5............. ...........5...........5..........4...........5
Does that make since? :)
Thank you bj
I will work with your formula to see if I can help.
Luke


"bj" wrote:

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K 2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2 =K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K 2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the
same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the
same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the
same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



"Luke" wrote:

Pardon the new thread about the same thing I posted earlier. I would
like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two
or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J & K
then
display contents of J in G. The only thing in the formula that chages
is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in simpler
format.





=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,IF(concatenate(C2,E2)=concatenate(I 2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2), I2,IF(concatenate(C2,E2)=concatenate(J2,I2),I2,IF( concatenate(C2,E2)=concatenate(J2,K2),I2,IF(concat enate(C2,E2)=concatenate(K2,I2),I2,IF(concatenate( C2,E2)=concatenate(K2,J2),I2,IF(concatenate(D2,C2) =concatenate(I2,J2),I2,IF(concatenate(D2,C2)=conca tenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate (J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2 ),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,I F(concatenate(D2,C2)=concatenate(K2,J2),I2,IF(conc atenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenat e(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E 2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=con catenate(J2,K2),I2,IF(concatenate(D2,E2)=concatena te(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2, J2),I2,IF(concatenate(E2,C2)=concatenate(I2,J2),I2 ,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(co ncatenate(E2,C2)=concatenate(J2,I2),I2,IF(concaten ate(E2,C2)=concatenate(J2,K2),I2,IF(concatenate(E2 ,C2)=concatenate(K2,I2),I2,IF(concatenate(E2,C2)=c oncatenate(K2,J2),I2,IF(concatenate(E2,D2)=concate nate(I2,J2),I2,IF(concatenate(E2,D2)=concatenate(I 2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I2), I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF( concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concat enate(E2,D2)=concatenate(K2,J2),I2,""))))))))))))) )))))))

Thank you for your time and toleration.
Luke




  #18   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #19   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

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" wrote in message
oups.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.



  #20   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.



  #21   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
oups.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.



  #22   Report Post  
Luke
 
Posts: n/a
Default

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" wrote in message
oups.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.




  #23   Report Post  
Biff
 
Posts: n/a
Default

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,SUMPRODUC T(--(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,SUMPRODUC T(--(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" wrote in message
...
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" wrote in message
oups.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.






  #24   Report Post  
Harlan Grove
 
Posts: n/a
Default

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


  #25   Report Post  
Harlan Grove
 
Posts: n/a
Default

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




  #26   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
oups.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.



  #27   Report Post  
Harlan Grove
 
Posts: n/a
Default

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


  #28   Report Post  
Luke
 
Posts: n/a
Default

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,SUMPRODUC T(--(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,SUMPRODUC T(--(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" wrote in message
...
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" wrote in message
oups.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.







  #29   Report Post  
Luke
 
Posts: n/a
Default

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($I72 79:$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" 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 | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _



  #30   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Luke" 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:$K727 9,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:$E72 79)))

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




  #31   Report Post  
Luke
 
Posts: n/a
Default

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



  #32   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

"Luke" wrote in message
...
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


  #33   Report Post  
Luke
 
Posts: n/a
Default

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" wrote in message
...
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



  #34   Report Post  
Luke
 
Posts: n/a
Default

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(CONCAT ENATE($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" 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,"")



  #35   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

....

You missed one of my earlier responses. See

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

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




  #36   Report Post  
Luke
 
Posts: n/a
Default

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(CONCAT ENATE($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" 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,"")



  #37   Report Post  
Luke
 
Posts: n/a
Default

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



  #38   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

....
=IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I 2,$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,"")


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula or not? Alan Excel Worksheet Functions 2 February 20th 05 04:26 PM
put formula results into a different cell if it is empty PutFormula Excel Worksheet Functions 2 February 11th 05 04:31 AM
how do i write a formula and keep in in formula form, so it DOESN. norcalchick2207 Excel Discussion (Misc queries) 2 February 4th 05 09:38 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Simplify Linking to several worksheets?? Geneva Excel Worksheet Functions 4 November 20th 04 06:41 PM


All times are GMT +1. The time now is 09:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"